package db;

import struct.*;

import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.util.ArrayList;

/**
 * DBManager class handles all interactions with the St.Patrick database
 * @author Gabrielle DuBois
 */
public class DBManager extends DB{

    /**
     * @see db.DB
     */
    public DBManager() throws Exception{
        super();
    }


    /**
     * Checks to see if a record exists in the database based on the passed in
     * primary key values. In the case of an adult or a dependent the method will
     * check both tables because an adult can not share the same primary key
     * as a dependent and vice versa.
     * @param classification a String which specifies where to obtain info from.
     *                      The options are: 
     *                      "adults" or
     *                      "dependents" or
     *                      "families" or
     *                      "address or
     *                      "ministries"
     * @param key a String array containing the values in order which
     *              corresponding with key for the given classification.
     * @return A boolean. True meaning the record does exist in the database.
     *                      False meaning the record does not exist in the
     *                      database.
     */
    public boolean doesExist(String classification, String[] key){
        ResultSet rs = null;
        PreparedStatement pstmt;
        String query;
        boolean exists = false;

        if (classification.equalsIgnoreCase("adults") || classification.equalsIgnoreCase("dependents")){
            try{
                //check in adults table
                query = "SELECT * FROM adults WHERE firstName=? AND lastName=? AND dob=?";
                pstmt = conn.prepareStatement(query);
                pstmt.setString(1, key[0]);
                pstmt.setString(2, key[1]);
                pstmt.setString(3, key[2]);

                //query to database
                rs = pstmt.executeQuery();
                conn.commit(); //clear locks

                if (rs.next()){     //if resultset isn't empty assume success
                    exists = true;
                }
                rs.close();

                //check in dependents table
                query = "SELECT * FROM dependents WHERE firstName=? AND lastName=? AND dob=?";
                pstmt = conn.prepareStatement(query);
                pstmt.setString(1, key[0]);
                pstmt.setString(2, key[1]);
                pstmt.setString(3, key[2]);

                //query to database
                rs = pstmt.executeQuery();
                conn.commit(); //clear locks

                if (rs.next()){     //if resultset isn't empty assume success
                    exists = true;
                }
                rs.close();
            }catch (SQLException e){
                System.err.println("Error in DBManager.doesExist() Adults or Dependents table: " + e);
            }
        }//if "adults" or "dependents"
        else if(classification.equalsIgnoreCase("families")){
            try{
                query = "SELECT * FROM "+ classification +
                        " WHERE familyName=? AND primaryPhone=?";
                pstmt = conn.prepareStatement(query);
                pstmt.setString(1, key[0]);
                pstmt.setString(2, key[1]);

                //query to database
                rs = pstmt.executeQuery();
                conn.commit(); //clear locks

                if (rs.next()){     //if resultset isn't empty assume success
                    exists = true;
                }
                rs.close();
            }catch (SQLException e){
                    System.err.println("Error in DBManager.doesExist() Families table: " + e);
            }
        }//elseif "families"
        else if(classification.equalsIgnoreCase("address")){
            try{
                query = "SELECT * FROM "+ classification +
                        " WHERE street=? AND zipcode=?";
                pstmt = conn.prepareStatement(query);
                pstmt.setString(1, key[0]);
                pstmt.setString(2, key[1]);

                //query to database
                rs = pstmt.executeQuery();
                conn.commit(); //clear locks

                if (rs.next()){     //if resultset isn't empty assume success
                    exists = true;
                }
                rs.close();
            }catch (SQLException e){
                    System.err.println("Error in DBManager.doesExist() Address table: " + e);
            }
        }//elseif "address"
        else if(classification.equalsIgnoreCase("ministries")){
            try{
                query = "SELECT * FROM "+ classification +
                        " WHERE ministryName=?";
                pstmt = conn.prepareStatement(query);
                pstmt.setString(1, key[0]);

                //query to database
                rs = pstmt.executeQuery();
                conn.commit(); //clear locks

                if (rs.next()){     //if resultset isn't empty assume success
                    exists = true;
                }
                rs.close();
            }catch (SQLException e){
                    System.err.println("Error in DBManager.doesExist() Ministry table: " + e);
            }
        }//elseif "ministries"

        return exists;
    }//doesExist()

    private boolean doesAddressCityStateExist(String table, String street, String zipcode){
        ResultSet rs = null;
        PreparedStatement pstmt;
        String query;
        boolean exists = false;

        if(table.equalsIgnoreCase("address")){
            try{
                query = "SELECT * FROM "+ table +
                        " WHERE street=? AND zipcode=?";
                pstmt = conn.prepareStatement(query);
                pstmt.setString(1, street);
                pstmt.setString(2, zipcode);

                //query to database
                rs = pstmt.executeQuery();
                conn.commit(); //clear locks

                if (rs.next()){     //if resultset isn't empty assume success
                    exists = true;
                }
                rs.close();
            }catch (SQLException e){
                    System.err.println("Error in private DBManager.doesAddressCityStateExist() Address table: " + e);
            }
        }//elseif "address"
        else if(table.equalsIgnoreCase("city_state")){
            try{
                query = "SELECT * FROM "+ table +
                        " WHERE zipcode=?";
                pstmt = conn.prepareStatement(query);
                pstmt.setString(1, zipcode);

                //query to database
                rs = pstmt.executeQuery();
                conn.commit(); //clear locks

                if (rs.next()){     //if resultset isn't empty assume success
                    exists = true;
                }
                rs.close();
            }catch (SQLException e){
                    System.err.println("Error in private DBManager.doesAddressCityStateExist() City_State table: " + e);
            }
        }//elseif "city_state"

        return exists;
    }//doesAddressCityStateExist()

    /**
     *
     * @param familyName
     * @param primaryPhoneNumber
     * @return
     */
    public int numberOfAdults(String familyName, String primaryPhoneNumber){
        int result = 0;
        ResultSet rs;
        PreparedStatement pstmt;
        String query;

        try{
                //check in adults table
                query = "SELECT a.firstName, a.lastName " +
                        "FROM adults a, family_composed_of f " +
                        "WHERE a.firstName=f.firstName AND a.lastName=f.lastName AND " +
                            "a.dob=f.dob AND familyName=? AND primaryPhone=? ";
                pstmt = conn.prepareStatement(query);
                pstmt.setString(1, familyName);
                pstmt.setString(2, primaryPhoneNumber);

                //query to database
                rs = pstmt.executeQuery();
                conn.commit(); //clear locks

                //each family must have at least one adult
                while(rs.next()){
                    //each family can have at most two adults
                    result++;
                }

                rs.close();
            }catch (SQLException e){
                System.err.println("Error in DBManager.numberOfAdults() Adults table: " + e);
            }
        
        return result;
    }//numberOfAdults()

    /**
     * 
     * @param primaryFamilyKey of the family/household which is acting as the base
     * @param secondaryFamilyKey of the family/household which is going to be
     *                              added to another family
     * @return A boolean - true if the method was successful and false otherwise
     */
    public boolean mergeHouseholds(String[] primaryFamilyKey, String[] secondaryFamilyKey){
        boolean didCommit = false;
        String delete;
        String update;
        String insert;
        String[] primaryAdultKey;
        String[] secondaryAdultKey;
        PreparedStatement pstmt;

        try{
            //delete secondary family money records
            delete = "DELETE FROM money "
                    + "WHERE familyName=? AND primaryPhone=?";
            pstmt = conn.prepareStatement(delete);
            pstmt.setString(1, secondaryFamilyKey[0]);
            pstmt.setString(2, secondaryFamilyKey[1]);
            pstmt.executeUpdate();
            pstmt.clearParameters();

            //delete secondary family fInterest records
            delete = "DELETE FROM fInterest "
                    + "WHERE familyName=? AND primaryPhone=?";
            pstmt = conn.prepareStatement(delete);
            pstmt.setString(1, secondaryFamilyKey[0]);
            pstmt.setString(2, secondaryFamilyKey[1]);
            pstmt.executeUpdate();
            pstmt.clearParameters();

            //delete secondary family fHistory records
            delete = "DELETE FROM fHistory "
                    + "WHERE familyName=? AND primaryPhone=?";
            pstmt = conn.prepareStatement(delete);
            pstmt.setString(1, secondaryFamilyKey[0]);
            pstmt.setString(2, secondaryFamilyKey[1]);
            pstmt.executeUpdate();
            pstmt.clearParameters();

            //set all secondary family addresses isCurrentMailing == false
            ArrayList<Address> oldAddresses = getAddresses(secondaryFamilyKey[0], secondaryFamilyKey[1]);
            for (Address a : oldAddresses){
                update = "UPDATE address "
                        + "SET isCurrentMailing=? "
                        + "WHERE street=? AND zipcode=?";
                pstmt = conn.prepareStatement(update);
                pstmt.setBoolean(1, false);
                pstmt.setString(2, a.getStreet());
                pstmt.setString(3, a.getZipcode());
                pstmt.executeUpdate();
                pstmt.clearParameters();
            }

            //change fam_has_address table
            update = "UPDATE fam_has_address "
                    + "SET familyName=?, primaryPhone=? "
                    + "WHERE familyName=? AND primaryPhone=?";
            pstmt = conn.prepareStatement(update);
            pstmt.setString(1, primaryFamilyKey[0]);
            pstmt.setString(2, primaryFamilyKey[1]);
            pstmt.setString(3, secondaryFamilyKey[0]);
            pstmt.setString(4, secondaryFamilyKey[1]);
            pstmt.executeUpdate();
            pstmt.clearParameters();

            //change in relationship_with table
            //Query for both adults in each family to add to in_relationship_with table
            primaryAdultKey = getSingleAdultInFamily(primaryFamilyKey);
            secondaryAdultKey = getSingleAdultInFamily(secondaryFamilyKey);
            insert = "INSERT INTO in_relationship_with VALUES(?,?,?,?,?,?)";
            pstmt = conn.prepareStatement(insert);
            pstmt.setString(1, primaryAdultKey[0]);
            pstmt.setString(2, primaryAdultKey[1]);
            pstmt.setString(3, primaryAdultKey[2]);
            pstmt.setString(4, secondaryAdultKey[0]);
            pstmt.setString(5, secondaryAdultKey[1]);
            pstmt.setString(6, secondaryAdultKey[2]);
            pstmt.executeUpdate();
            pstmt.clearParameters();

            //change lives_with table
            ArrayList<Dependent> primaryDependents = getDependents(primaryFamilyKey[0], primaryFamilyKey[1]);
            ArrayList<Dependent> secondaryDependents = getDependents(primaryFamilyKey[0], primaryFamilyKey[1]);
            
            //insert primaryAdultKey with secondaryDependents
            for (Dependent d : secondaryDependents){
                insert = "INSERT INTO lives_with VALUES(?,?,?,?,?,?)";
                pstmt = conn.prepareStatement(insert);
                pstmt.setString(1, primaryAdultKey[0]);
                pstmt.setString(2, primaryAdultKey[1]);
                pstmt.setString(3, primaryAdultKey[2]);

                pstmt.setString(4,d.getFirstName());
                pstmt.setString(5,d.getLastName());
                pstmt.setString(6,d.getDateOfBirth());

                pstmt.executeUpdate();
                pstmt.clearParameters();
            }
            
            //insert secondaryAdultKey with primaryDependents
            for (Dependent d : primaryDependents){
                insert = "INSERT INTO lives_with VALUES(?,?,?,?,?,?)";
                pstmt = conn.prepareStatement(insert);
                pstmt.setString(1, secondaryAdultKey[0]);
                pstmt.setString(2, secondaryAdultKey[1]);
                pstmt.setString(3, secondaryAdultKey[2]);

                pstmt.setString(4,d.getFirstName());
                pstmt.setString(5,d.getLastName());
                pstmt.setString(6,d.getDateOfBirth());

                pstmt.executeUpdate();
                pstmt.clearParameters();
            }

            //change fam_composed_of table
            update = "UPDATE family_composed_of "
                    + "SET familyName=?, primaryPhone=? "
                    + "WHERE familyName=? AND primaryPhone=?";
            pstmt = conn.prepareStatement(update);
            pstmt.setString(1, primaryFamilyKey[0]);
            pstmt.setString(2, primaryFamilyKey[1]);
            pstmt.setString(3, secondaryFamilyKey[0]);
            pstmt.setString(4, secondaryFamilyKey[1]);
            pstmt.executeUpdate();
            pstmt.clearParameters();

            //delete old family information
            delete = "DELETE FROM families "
                    + "WHERE familyName=? AND primaryPhone=?";
            pstmt = conn.prepareStatement(delete);
            pstmt.setString(1, secondaryFamilyKey[0]);
            pstmt.setString(2, secondaryFamilyKey[1]);
            pstmt.executeUpdate();
            pstmt.clearParameters();

           //Commit to database
            conn.commit();
            didCommit = true;

        }catch(SQLException e){
            System.err.println("Error in DBManager.mergeHouseholds(): " + e);
            try {
              System.err.println("Transaction is being rolled back");
              conn.rollback();
            } catch(SQLException e2) {
              System.err.println("Rolling back transcation unsucessful");
            }
        }

        return didCommit;
    }//mergeHouseholds()

    private String[] getSingleAdultInFamily(String[] familyKey) throws SQLException{
        String[] adultKey = new String[3];
        String query;
        PreparedStatement pstmt;
        ResultSet rs;
        
        //Query for both adults in each family to add to in_relationship_with table
            query = "SELECT a.firstName, a.lastName, a.dob "
                    + "FROM family_composed_of f, adults a "
                    + "WHERE a.firstName=f.firstName AND a.lastName=f.lastName AND "
                    + "a.dob=f.dob AND familyName=? AND primaryPhone=?";
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, familyKey[0]);
            pstmt.setString(2, familyKey[1]);
            rs = pstmt.executeQuery();
            pstmt.clearParameters();

            if (rs.next()){
                adultKey[0] = rs.getString("firstName");
                adultKey[1] = rs.getString("lastName");
                adultKey[2] = rs.getString("dob");
            }
            rs.close();

        return adultKey;
    }


    
//ADD METHODS
    /**
     *
     * @param classification a String which specifies where to obtain info from.
     *                  The options are: 
     *                  " " == none
     *                  "interest" or "finterest (family interest)
     *                  "history" or "fhistory" (family history)
     *                  "ainterest" (adult interest)
     *                  "ahistory" (adult history)
     *                  "dinterest" (dependent interest)
     *                  "dhistory" (dependent history)
     * @param record
     * @param personORfamilyKey
     * @return A boolean - true if the method was successful and false otherwise
     */
    public boolean addRecord(String classification, DBObject record, String[] personORfamilyKey){
        boolean didAdd = true;

        try{
            if (record instanceof Adult){
                addAdult((Adult) record, personORfamilyKey);
            }else if (record instanceof Dependent){
                addDependent((Dependent)record, personORfamilyKey);
            }else if (record instanceof Address){
                addAddress((Address)record, personORfamilyKey);
            }else if (record instanceof Money){
                addMoney((Money) record, personORfamilyKey);
            }else if (record instanceof Interest){
                if (classification.length()>8)  //"interest" == 7 characters
                    addInterest(classification.charAt(0)+"", (Interest) record, personORfamilyKey);
                else
                    addInterest("f", (Interest) record, personORfamilyKey);
            }else if (record instanceof History){
                if (classification.length()>7) //"history" == 7 characters
                    addHistory(classification.charAt(0)+"", (History) record, personORfamilyKey);
                else
                    addHistory("f", (History) record, personORfamilyKey);
            }

        }catch(SQLException e){
            didAdd = false;
            System.err.println("Error in DBManager.addRecord(): " + e);
            try {
              System.err.println("Transaction is being rolled back");
              conn.rollback();
            } catch(SQLException e2) {
              System.err.println("Rolling back transcation unsucessful");
            }
        }
        //DO NOT COMMIT!!

        return didAdd;
    }//addRecord()

    /**
     *
     * @param adults
     * @param newDependents
     * @param existingDependents
     * @param family
     * @param addresses
     * @param monies
     * @param interests
     * @param histories
     * @return A boolean - true if the method was successful and false otherwise
     */
    public boolean addHousehold(Adult[] adults, Dependent[] newDependents,
                                    Dependent[] existingDependents,
                                    Family family, Address[] addresses,
                                    Money[] monies, Interest[] interests,
                                    History[] histories){
        boolean didCommit = false;
        String update;
        PreparedStatement pstmt;
        Object[] attributes;

        try{
        //ADD TO ADULTS TABLE
            for (int i=0; i<adults.length; i++){
                attributes = adults[i].getAttributes();

                update = "INSERT INTO adults VALUES(?,?,?,?,?,?,?,?,?,?)";
                pstmt = conn.prepareStatement(update);
                for (int j=0; j<attributes.length; j++){
                    pstmt.setString(j+1, (String)attributes[j]);
                }//for(j)

                pstmt.executeUpdate();
            }//for(i)

        //ADD TO IN_RELATIONSHIP_WITH TABLE
            if (adults.length > 1){
                //Add Adults to In_Relationship_With table if more than one
                update = "INSERT INTO in_relationship_with VALUES(?,?,?,?,?,?)";
                pstmt = conn.prepareStatement(update);
                pstmt.setString(1,adults[0].getFirstName());
                pstmt.setString(2,adults[0].getLastName());
                pstmt.setString(3,adults[0].getDateOfBirth());

                pstmt.setString(4,adults[1].getFirstName());
                pstmt.setString(5,adults[1].getLastName());
                pstmt.setString(6,adults[1].getDateOfBirth());

                pstmt.executeUpdate();
            }//if more than one adult

         //ADD TO DEPENDENTS TABLE
            for (int i=0; i<newDependents.length; i++){
                //Only add newDependents
                attributes = newDependents[i].getAttributes();

                update = "INSERT INTO dependents VALUES(?,?,?,?,?,?,?)";
                pstmt = conn.prepareStatement(update);
                for (int j=0; j<attributes.length; j++){
                    pstmt.setString(j+1, (String)attributes[j]);
                }//for(j)

                pstmt.executeUpdate();
            }//for(i)

        //ADD TO LIVES_WITH TABLE
            if (newDependents.length > 0 || existingDependents.length > 0){
                update = "INSERT INTO lives_with VALUES(?,?,?,?,?,?)";
                pstmt = conn.prepareStatement(update);
                for(int i=0; i<adults.length; i++){
                    // Add newDependents to Lives_With
                    for (int j=0; j<newDependents.length; j++){
                        pstmt.setString(1,adults[i].getFirstName());
                        pstmt.setString(2,adults[i].getLastName());
                        pstmt.setString(3,adults[i].getDateOfBirth());

                        pstmt.setString(4,newDependents[j].getFirstName());
                        pstmt.setString(5,newDependents[j].getLastName());
                        pstmt.setString(6,newDependents[j].getDateOfBirth());

                        pstmt.executeUpdate();
                    }//for(j)
                    // Add existingDependents to Lives_With
                    for (int j=0; j<existingDependents.length; j++){
                        pstmt.setString(1,adults[i].getFirstName());
                        pstmt.setString(2,adults[i].getLastName());
                        pstmt.setString(3,adults[i].getDateOfBirth());

                        pstmt.setString(4,existingDependents[j].getFirstName());
                        pstmt.setString(5,existingDependents[j].getLastName());
                        pstmt.setString(6,existingDependents[j].getDateOfBirth());

                        pstmt.executeUpdate();
                    }//for(j)
                }//for(i)
            }//if there are dependents to add

        //ADD TO FAMILIES TABLE
            Object[] fAtts = family.getAttributes();
            update = "INSERT INTO families VALUES(?,?,?,?,?,?,?)";
            pstmt = conn.prepareStatement(update);
            pstmt.setString(1, (String) fAtts[0]);
            pstmt.setString(2, (String) fAtts[1]);
            pstmt.setString(3, (String) fAtts[2]);
            pstmt.setBoolean(4, (Boolean) fAtts[3]);
            pstmt.setBoolean(5, (Boolean) fAtts[4]);
            pstmt.setString(6, (String) fAtts[5]);
            pstmt.setString(7, (String) fAtts[6]);

            pstmt.executeUpdate();
            pstmt.clearParameters();

        //ADD TO FAMILY_COMPOSED_OF TABLE
            update = "INSERT INTO family_composed_of VALUES(?,?,?,?,?)";
            pstmt = conn.prepareStatement(update);
            //put adults into family_composed_of
            for (int i=0; i<adults.length; i++){
                pstmt.setString(1, (String) fAtts[0]);
                pstmt.setString(2, (String) fAtts[1]);
                pstmt.setString(3, adults[i].getFirstName());
                pstmt.setString(4, adults[i].getLastName());
                pstmt.setString(5, adults[i].getDateOfBirth());

                pstmt.executeUpdate();
                pstmt.clearParameters();
            }//for(i)
            //put new dependents into family_composed_of
            for (int j=0; j<newDependents.length; j++){
                pstmt.setString(1, (String) fAtts[0]);
                pstmt.setString(2, (String) fAtts[1]);
                pstmt.setString(3, newDependents[j].getFirstName());
                pstmt.setString(4, newDependents[j].getLastName());
                pstmt.setString(5, newDependents[j].getDateOfBirth());

                pstmt.executeUpdate();
                pstmt.clearParameters();
            }//for(j)
            //put existing dependents into family_composed_of
            for (int j=0; j<existingDependents.length; j++){
                pstmt.setString(1, (String) fAtts[0]);
                pstmt.setString(2, (String) fAtts[1]);
                pstmt.setString(3, existingDependents[j].getFirstName());
                pstmt.setString(4, existingDependents[j].getLastName());
                pstmt.setString(5, existingDependents[j].getDateOfBirth());

                pstmt.executeUpdate();
                pstmt.clearParameters();
            }//for(j)

        //ADD TO FINTEREST
            update = "INSERT INTO finterest VALUES(?,?,?,?)";
            pstmt = conn.prepareStatement(update);
            for (int i=0; i<interests.length; i++){
                attributes = interests[i].getAttributes();
                pstmt.setString(1, family.getFamilyName());             //familyName
                pstmt.setString(2, family.getPrimaryPhoneNumber());     //familPrimaryPhone
                pstmt.setString(3, (String) attributes[0]);             //ministryName
                pstmt.setString(4, (String) attributes[1]);             //availability

                pstmt.executeUpdate();
            }//for(i)

        //ADD TO FHISTORY TABLE
            // Add to fHistory table (5 attributes)
            update = "INSERT INTO fhistory VALUES(?,?,?,?)";
            pstmt = conn.prepareStatement(update);
            for (int i=0; i<histories.length; i++){
                attributes = histories[i].getAttributes();
                pstmt.setString(1, family.getFamilyName());             //familyName
                pstmt.setString(2, family.getPrimaryPhoneNumber());     //familyPrimaryPhone
                pstmt.setString(3, (String) attributes[0]);             //ministryName
                pstmt.setString(4, (String) attributes[1]);             //date

                pstmt.executeUpdate();
            }//for(i)

        //ADD TO ADDRESS TABLE
                update = "INSERT INTO address VALUES(?,?,?,?,?)";
                pstmt = conn.prepareStatement(update);

            for (int i=0; i<addresses.length; i++){
                attributes = addresses[i].getAttributes();
                if (!doesAddressCityStateExist("address", (String)attributes[0], (String)attributes[3] )){
                    pstmt.setString(1, (String) attributes[0]);     //street
                    pstmt.setString(2, (String) attributes[3]);     //zipcode
                    pstmt.setString(3, (String) attributes[4]);     //municipal
                    pstmt.setBoolean(4, (Boolean) attributes[5]);   //isCurrentMailing
                    pstmt.setString(5, (String) attributes[6]);     //notes

                    pstmt.executeUpdate();
                }//if doesn't already exist add it
            }//for(i)

        //ADD TO CITY_STATE TABLE
            update = "INSERT INTO city_state VALUES(?,?,?)";
            pstmt = conn.prepareStatement(update);
            for (int i=0; i<addresses.length; i++){
                attributes = addresses[i].getAttributes();
                if (!doesAddressCityStateExist("city_state", null, (String) attributes[3] )){
                    pstmt.setString(2, (String) attributes[1]);      //city
                    pstmt.setString(3, (String) attributes[2]);      //state
                    pstmt.setString(1, (String) attributes[3]);      //zipcode

                    pstmt.executeUpdate();
                }//if doesn't already exist add it
            }//for(i)

        //ADD TO FAM_HAS_ADDRESS TABLE
            update = "INSERT INTO Fam_Has_Address VALUES(?,?,?,?)";
            pstmt = conn.prepareStatement(update);

            for (int i=0; i<addresses.length; i++){
                pstmt.setString(1, (String) addresses[i].getStreet());  //street
                pstmt.setString(2, (String) addresses[i].getZipcode()); //zipcode
                pstmt.setString(3, family.getFamilyName());             //familyName
                pstmt.setString(4, family.getPrimaryPhoneNumber());     //primaryPhone

                pstmt.executeUpdate();
            }//for(i)

        //ADD TO MONEY TABLE
            update = "INSERT INTO money VALUES(?,?,?,?,?,?)";
            pstmt = conn.prepareStatement(update);

            for (int i=0; i<monies.length; i++){
                attributes = monies[i].getAttributes();
                pstmt.setString(1, (String) attributes[0]);
                pstmt.setString(2, family.getFamilyName());
                pstmt.setString(3, family.getPrimaryPhoneNumber());
                pstmt.setDouble(4, ((Double)attributes[1]).doubleValue() );  //pledge
                pstmt.setDouble(5, ((Double)attributes[2]).doubleValue() );  //year2date
                pstmt.setString(6, (String) attributes[3]);             //notes

                pstmt.executeUpdate();
            }//for(i)

            //Commit to database
            conn.commit();
            didCommit = true;
        }catch(SQLException e){
            System.err.println("Error in DBManager.addHousehold(): " + e);
            try {
              System.err.println("Transaction is being rolled back");
              conn.rollback();
            } catch(SQLException e2) {
              System.err.println("Rolling back transcation unsucessful");
            }
        }

        return didCommit;
    }//addHousehold()
    
    
    private void addAdult(Adult a, String[] famKey) throws SQLException{
        String update;
        String query;
        PreparedStatement pstmt;
        ResultSet rs;
        Object[] attrib = a.getAttributes();
        String[] aKey = a.getKey();
        String[] person = new String[3];
        ArrayList<String[]> dependents = new ArrayList<String[]>();
        
        //Add to Adults table
        update = "INSERT INTO adults VALUES(?,?,?,?,?,?,?,?,?,?)";
        pstmt = conn.prepareStatement(update);
        for (int j=0; j<attrib.length; j++){
            pstmt.setString(j+1, (String) attrib[j]);
        }//for(j)
        pstmt.executeUpdate();
        pstmt.clearParameters();

        //Query for other adults in family to add to in_relationship_with table
        query = "SELECT a.firstName, a.lastName, a.dob "
                + "FROM family_composed_of f, adults a "
                + "WHERE a.firstName=f.firstName AND a.lastName=f.lastName AND "
                + "a.dob=f.dob AND a.firstName<>? AND a.lastName<>? AND "
                + "a.dob<>? AND familyName=? AND primaryPhone=?";
        pstmt = conn.prepareStatement(query);
        pstmt.setString(1,aKey[0]);
        pstmt.setString(2,aKey[1]);
        pstmt.setString(3,aKey[2]);
        pstmt.setString(4, famKey[0]);
        pstmt.setString(5, famKey[1]);
        rs = pstmt.executeQuery();
        pstmt.clearParameters();

        if (rs.next()){
            person[0] = rs.getString("firstName");
            person[1] = rs.getString("lastName");
            person[2] = rs.getString("dob");

            //Add Adults to In_Relationship_With table
            update = "INSERT INTO in_relationship_with VALUES(?,?,?,?,?,?)";
            pstmt = conn.prepareStatement(update);
            pstmt.setString(1,aKey[0]);
            pstmt.setString(2,aKey[1]);
            pstmt.setString(3,aKey[2]);
            pstmt.setString(4,person[0]);
            pstmt.setString(5,person[1]);
            pstmt.setString(6,person[2]);
            pstmt.executeUpdate();
            pstmt.clearParameters();
        }

        //Add Adult to Family_Composed_Of
        update = "INSERT INTO family_composed_of VALUES(?,?,?,?,?)";
        pstmt = conn.prepareStatement(update);
        pstmt.setString(1,famKey[0]);
        pstmt.setString(2,famKey[1]);
        pstmt.setString(3,aKey[0]);
        pstmt.setString(4,aKey[1]);
        pstmt.setString(5,aKey[2]);
        
        pstmt.executeUpdate();
        pstmt.clearParameters();

        //Query dependents in family to add to lives_with table
        query = "SELECT d.firstName, d.lastName, d.dob "
                + "FROM family_composed_of f, dependents d "
                + "WHERE d.firstName=f.firstName AND d.lastName=f.lastName AND "
                + "d.dob=f.dob AND familyName=? AND primaryPhone=?";
        pstmt = conn.prepareStatement(query);
        pstmt.setString(1, famKey[0]);
        pstmt.setString(2, famKey[1]);

        rs = pstmt.executeQuery();
        pstmt.clearParameters();

        if (rs.next()){
            do{
                person[0] = rs.getString("firstName");
                person[1] = rs.getString("lastName");
                person[2] = rs.getString("dob");
                dependents.add(person);
            }while(rs.next());

            //Add Adult and Dependents to Lives_With table
            update = "INSERT INTO lives_with VALUES(?,?,?,?,?,?)";
            pstmt = conn.prepareStatement(update);
            for(String[] d : dependents){
                pstmt.setString(1,aKey[0]);
                pstmt.setString(2,aKey[1]);
                pstmt.setString(3,aKey[2]);
                pstmt.setString(4,d[0]);
                pstmt.setString(5,d[1]);
                pstmt.setString(6,d[2]);
                pstmt.executeUpdate();
                pstmt.clearParameters();
            }//foreach string[] in dependents
        }

        //DO NOT COMMIT!!
    }//addAdult()

    private void addDependent(Dependent d, String[] famKey) throws SQLException{
        String update;
        String query;
        PreparedStatement pstmt;
        ResultSet rs;
        Object[] attrib = d.getAttributes();
        String[] dKey = d.getKey();
        String[] aKey = new String[3];
        ArrayList<String[]> adults = new ArrayList<String[]>();
        
        // Add to Dependents table
        update = "INSERT INTO dependents VALUES(?,?,?,?,?,?,?)";
        pstmt = conn.prepareStatement(update);
        for (int j=0; j<attrib.length; j++){
            pstmt.setString(j+1, (String) attrib[j]);
        }//for(j)
        pstmt.executeUpdate();
        pstmt.clearParameters();

        //Query Adults in family so can add to Lives_With
        query = "SELECT a.firstName, a.lastName, a.dob "
                + "FROM family_composed_of f, adults a "
                + "WHERE a.firstName=f.firstName AND a.lastName=f.lastName AND "
                + "a.dob=f.dob AND familyName=? AND primaryPhone=?";
        pstmt = conn.prepareStatement(query);
        pstmt.setString(1, famKey[0]);
        pstmt.setString(2, famKey[1]);

        rs = pstmt.executeQuery();
        pstmt.clearParameters();

        if (rs.next()){
            do{
                aKey[0] = rs.getString("firstName");
                aKey[1] = rs.getString("lastName");
                aKey[2] = rs.getString("dob");
                adults.add(aKey);
            }while(rs.next());

            //Add Dependent to Lives_With
            update = "INSERT INTO lives_with VALUES(?,?,?,?,?,?)";
            pstmt = conn.prepareStatement(update);
            for(String[] a : adults){
                pstmt.setString(1,a[0]);
                pstmt.setString(2,a[1]);
                pstmt.setString(3,a[2]);

                pstmt.setString(4,dKey[0]);
                pstmt.setString(5,dKey[1]);
                pstmt.setString(6,dKey[2]);

                pstmt.executeUpdate();
                pstmt.clearParameters();
            }//foreach string[] in adults
        }//if(rs.next())

        //Add Dependent to Family_Composed_Of
        update = "INSERT INTO family_composed_of VALUES(?,?,?,?,?)";
        pstmt = conn.prepareStatement(update);
        pstmt.setString(1,famKey[0]);
        pstmt.setString(2,famKey[1]);
        pstmt.setString(3,dKey[0]);
        pstmt.setString(4,dKey[1]);
        pstmt.setString(5,dKey[2]);
        pstmt.executeUpdate();
        pstmt.clearParameters();
        
        //DO NOT COMMIT!!
    }//addDependent()

    private void addAddress(Address a, String[] famKey) throws SQLException{
        String update;
        PreparedStatement pstmt;
        Object[] attribs = a.getAttributes();

        //Note: Families CAN NOT share addresses.

        //Add to Address table (5 attributes)
        update = "INSERT INTO address VALUES(?,?,?,?,?)";
        pstmt = conn.prepareStatement(update);

        pstmt.setString(1, (String) attribs[0]);          //street
        pstmt.setString(2, (String) attribs[3]);          //zipcode
        pstmt.setString(3, (String) attribs[4]);          //municipal
        pstmt.setBoolean(4, (Boolean) attribs[5]);        //isCurrentMailing
        pstmt.setString(5, (String) attribs[6]);          //notes
        pstmt.executeUpdate();
        pstmt.executeUpdate();


        try{
            //Add to City_State table (3 attributes)
            update = "INSERT INTO city_state VALUES(?,?,?)";
            pstmt = conn.prepareStatement(update);

            pstmt.setString(2, (String) attribs[1]);      //city
            pstmt.setString(3, (String) attribs[2]);      //state
            pstmt.setString(1, (String) attribs[3]);      //zipcode

            pstmt.executeUpdate();
            pstmt.clearParameters();
        }catch (SQLException e){
            //Do nothing. Zipcode already exists in db
        }

        //Add to Fam_Has_Address table (4 attributes)
        update = "INSERT INTO Fam_Has_Address VALUES(?,?,?,?)";
        pstmt = conn.prepareStatement(update);

        pstmt.setString(1, a.getStreet());  //street
        pstmt.setString(2, a.getZipcode()); //zipcode
        pstmt.setString(3, famKey[0]);      //familyName
        pstmt.setString(4, famKey[1]);      //primaryPhone

        pstmt.executeUpdate();
        pstmt.clearParameters();

        //DO NOT COMMIT!!
    }//addAddress()

    private void addMoney(Money m, String[] famKey) throws SQLException{
        String update;
        PreparedStatement pstmt;
        Object[] attrib = m.getAttributes();
        
        //Add to Money table (6 attributes)
        update = "INSERT INTO money VALUES(?,?,?,?,?,?)";
        pstmt = conn.prepareStatement(update);

        pstmt.setString(1, (String) attrib[0]);
        pstmt.setString(2, famKey[0]);
        pstmt.setString(3, famKey[1]);
        pstmt.setDouble(4, ((Double)attrib[1]).doubleValue() );      //pledged
        pstmt.setDouble(5, ((Double)attrib[2]).doubleValue() );      //year2date
        pstmt.setString(6, (String)attrib[3]);                  //notes

        pstmt.executeUpdate();
        pstmt.clearParameters();
        
        //DO NOT COMMIT!!
    }//addMoney()

    private void addInterest(String prefix, Interest i, String[] key) throws SQLException{
        String table = prefix + "Interest";
        String update;
        PreparedStatement pstmt;
        Object[] attrib = i.getAttributes();

        if (prefix.equalsIgnoreCase("f")){
            update = "INSERT INTO "+table+" VALUES(?,?,?,?)";
            pstmt = conn.prepareStatement(update);

            pstmt.setString(1, key[0]);
            pstmt.setString(2, key[1]);
            pstmt.setString(3, (String) attrib[0]);
            pstmt.setString(4, (String) attrib[1]);

            pstmt.executeUpdate();
            pstmt.clearParameters();
        }else if (prefix.equalsIgnoreCase("a") || prefix.equalsIgnoreCase("d")){
            update = "INSERT INTO "+table+" VALUES(?,?,?,?,?)";
            pstmt = conn.prepareStatement(update);

            pstmt.setString(1, key[0]);             //firstName
            pstmt.setString(2, key[1]);             //lastName
            pstmt.setString(3, key[2]);             //dob
            pstmt.setString(4, (String) attrib[0]); //ministryName
            pstmt.setString(5, (String) attrib[1]); //availibitly

            pstmt.executeUpdate();
            pstmt.clearParameters();
        }

        //DO NOT COMMIT!!
    }//addInterest()

    private void addHistory(String prefix, History h, String[] key) throws SQLException{
        String table = prefix + "History";
        String update;
        PreparedStatement pstmt;
        Object[] attrib = h.getAttributes();

        if (prefix.equalsIgnoreCase("f")){
            update = "INSERT INTO "+table+" VALUES(?,?,?,?)";
            pstmt = conn.prepareStatement(update);

            pstmt.setString(1, key[0]);
            pstmt.setString(2, key[1]);
            pstmt.setString(3, (String) attrib[0]);
            pstmt.setString(4, (String) attrib[1]);

            pstmt.executeUpdate();
            pstmt.clearParameters();
        }else if (prefix.equalsIgnoreCase("a") || prefix.equalsIgnoreCase("d")){
            update = "INSERT INTO "+table+" VALUES(?,?,?,?,?,?)";
            pstmt = conn.prepareStatement(update);

            pstmt.setString(1, key[0]);
            pstmt.setString(2, key[1]);
            pstmt.setString(3, key[2]);
            pstmt.setString(4, (String) attrib[0]);
            pstmt.setString(5, (String) attrib[1]);
            pstmt.setString(6, (String) attrib[2]);

            pstmt.executeUpdate();
            pstmt.clearParameters();
        }

        //DO NOT COMMIT!!
    }//addInterest()



//GET OBJECT METHODS
    /**
     *
     * @param familyName a String containing the name of the family in question
     * @param primaryPhone a String containing the family's primary phone number
     * @return A Family object containing the attributes of that family
     */
    public Family getFamily(String familyName, String primaryPhone){
        ResultSet rs = null;
        PreparedStatement pstmt;
        Family family = null;
        String query;

        try{
            query = "SELECT * FROM families WHERE familyName=? AND primaryPhone=?";
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, familyName);
            pstmt.setString(2, primaryPhone);

            //query to database
            rs = pstmt.executeQuery();
            conn.commit(); //clear locks

            //double check to make sure the ResultSet is not empty
            if (rs.next()){
                family = new Family( rs.getString("familyName"), rs.getString("primaryPhone"),
                                    rs.getString("headOfHouse"), rs.getBoolean("sendDonateLetter"),
                                    rs.getBoolean("sendMinistryLetter"), rs.getString("salutation"),
                                    rs.getString("notes") );
            }
            rs.close();
        }catch (SQLException e){
            System.err.println("Error in DBManager.getFamily(): " + e);
        }

        return family;
    }//getFamily()

    /**
     *
     * @param familyName a String containing the name of the family in question
     * @param primaryPhone a String containing the family's primary phone number
     * @return An ArrayList of Adult objects containing the adults in specified
     *              family with their attributes
     */
    public ArrayList<Adult> getAdults(String familyName, String primaryPhone){
        ResultSet rs = null;
        PreparedStatement pstmt;
        ArrayList<Adult> adults = new ArrayList<Adult>();
        String query;

        try{
            query = "SELECT DISTINCT a.firstName, a.lastName, a.dob, " +
                    "a.dateJoined, a.occupation, a.employer, a.workPhone, " +
                    "a.personalPhone, a.email, a.notes " +
                    "FROM adults a, family_composed_of f " +
                    "WHERE a.firstName=f.firstName AND a.lastName=f.lastName " +
                    "AND a.dob=f.dob AND familyName=? AND primaryPhone=?";
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, familyName);
            pstmt.setString(2, primaryPhone);

            //query to database
            rs = pstmt.executeQuery();
            conn.commit(); //clear locks

            //double check to make sure the ResultSet is not empty
            while (rs.next()){
                adults.add( new Adult( rs.getString("firstName"), rs.getString("lastName"),
                                    rs.getString("dob"), rs.getString("dateJoined"),
                                    rs.getString("occupation"), rs.getString("employer"),
                                    rs.getString("workPhone"), rs.getString("personalPhone"),
                                    rs.getString("email"), rs.getString("notes") ) );
            }
            rs.close();
        }catch (SQLException e){
            System.err.println("Error in DBManager.getAdults(): " + e);
        }

        return adults;
    }//getAdults()

    /**
     *
     * @param familyName a String containing the name of the family in question
     * @param primaryPhone a String containing the family's primary phone number
     * @return An ArrayList of Dependent objects containing the dependents in specified
     *              family with their attributes
     */
    public ArrayList<Dependent> getDependents(String familyName, String primaryPhone){
        ResultSet rs = null;
        PreparedStatement pstmt;
        ArrayList<Dependent> deps = new ArrayList<Dependent>();
        String query;

        try{
            query = "SELECT DISTINCT d.firstName, d.lastName, d.dob, " +
                    "d.school, d.religiousSchooling, d.personalPhone, d.notes " +
                    "FROM dependents d, family_composed_of f " +
                    "WHERE d.firstName=f.firstName AND d.lastName=f.lastName " +
                    "AND d.dob=f.dob AND familyName=? AND primaryPhone=?";
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, familyName);
            pstmt.setString(2, primaryPhone);

            //query to database
            rs = pstmt.executeQuery();
            conn.commit(); //clear locks

            //double check to make sure the ResultSet is not empty
            while (rs.next()){
                deps.add( new Dependent( rs.getString("firstName"), rs.getString("lastName"),
                                        rs.getString("dob"), rs.getString("school"),
                                        rs.getString("religiousSchooling"),
                                        rs.getString("personalPhone"),
                                        rs.getString("notes") ) );
            }
            rs.close();
        }catch (SQLException e){
            System.err.println("Error in DBManager.getDependents(): " + e);
        }

        return deps;
    }//getDependents()

    /**
     *
     * @param familyName a String containing the name of the family in question
     * @param primaryPhone a String containing the family's primary phone number
     * @return An ArrayList of Address objects containing the addresses in specified
     *              family with their attributes
     */
    public ArrayList<Address> getAddresses(String familyName, String primaryPhone){
        ResultSet rs = null;
        PreparedStatement pstmt;
        ArrayList<Address> addys = new ArrayList<Address>();
        String query;

        try{
            query = "SELECT a.street, cs.city, cs.state, " +
                        "a.zipcode, a.municipal, a.isCurrentMailing, a.notes " +
                        "FROM address a, city_state cs, fam_has_address f " +
                        "WHERE a.zipcode=cs.zipcode AND " +
                        "f.street=a.street AND " +
                        "familyName=? AND primaryPhone=?";
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, familyName);
            pstmt.setString(2, primaryPhone);

            //query to database
            rs = pstmt.executeQuery();
            conn.commit(); //clear locks

            //double check to make sure the ResultSet is not empty
            while (rs.next()){
               addys.add( new Address(  rs.getString(1), rs.getString(2),
                                        rs.getString(3), rs.getString(4),
                                        rs.getString(5), rs.getBoolean(6),
                                        rs.getString(7)) );
            }
            rs.close();
        }catch (SQLException e){
            System.err.println("Error in DBManager.getAddresses(): " + e);
        }

        return addys;
    }//getAddresses()

    /**
     *
     * @param familyName a String containing the name of the family in question
     * @param primaryPhone a String containing the family's primary phone number
     * @return An ArrayList of Money objects containing the monies(donations
     *              record) in specified family with their attributes
     */
    public ArrayList<Money> getMonies(String familyName, String primaryPhone){
        ResultSet rs = null;
        PreparedStatement pstmt;
        ArrayList<Money> m = new ArrayList<Money>();
        String query;

        try{
            query = "SELECT * " +
                        "FROM money " +
                        "WHERE familyName=? AND primaryPhone=?  ORDER BY theYear";
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, familyName);
            pstmt.setString(2, primaryPhone);

            //query to database
            rs = pstmt.executeQuery();
            conn.commit(); //clear locks

            //double check to make sure the ResultSet is not empty
            while (rs.next()){
               m.add( new Money(  rs.getString("theYear"), rs.getDouble("pledged"),
                                        rs.getDouble("year2date"), rs.getString("notes")) );
            }

            rs.close();
        }catch (SQLException e){
            System.err.println("Error in DBManager.getMonies(): " + e);
        }

        return m;
    }//getMonies()

    /**
     * Gets ministry interest for a family or an individual.
     * @param classification a String which specifies where to obtain info from.
     *                  The options are: "adults" or "dependents" or "families"
     * @param key an array String containing the attributes values
     *                  that make up a key for the given classification
     * @return A ArrayList containing Interest objects with all the interest
     *                  information for an individual or family depending on
     *                  which one was asked for
     */
    public ArrayList<Interest> getInterests(String classification, String[] key){
        PreparedStatement pstmt;
        ResultSet rs = null;
        String prefix;
        String iSuffix = "Interest";
        String iQuery;
        ArrayList<Interest> interests = new ArrayList<Interest>();

        if (classification.equalsIgnoreCase("adults"))
            prefix = "a";
        else if (classification.equalsIgnoreCase("dependents"))
            prefix = "d";
        else //(table.equalsIgnoreCase("families"))
            prefix = "f";

        if (classification.equalsIgnoreCase("adults") || classification.equalsIgnoreCase("dependents")){
            try{
                //Get the ministry Interest info
                iQuery = "SELECT * FROM "+prefix+iSuffix+" WHERE firstName=? " +
                        "AND lastName=? AND dob=? ORDER BY lastName";
                pstmt = conn.prepareStatement(iQuery);
                pstmt.setString(1, key[0]);
                pstmt.setString(2, key[1]);
                pstmt.setString(3, key[2]);

                //query to database
                rs = pstmt.executeQuery();
                conn.commit(); //clear locks

                //loop through ResultSet gathering interest info
                while (rs.next()){
                     interests.add(new Interest( rs.getString("ministryName"),
                                                rs.getString("availability") ));
                }

                rs.close();
            }catch (SQLException e){
                System.err.println("Error in DBManager.getInterests() Adults/Dependents table: " + e);
            }
        }else if (classification.equalsIgnoreCase("families")){
            try{
                //Get the ministry Interest info
                iQuery = "SELECT * FROM "+prefix+iSuffix+" WHERE familyName=? " +
                        "AND primaryPhone=? ORDER BY familyName";
                pstmt = conn.prepareStatement(iQuery);
                pstmt.setString(1, key[0]);
                pstmt.setString(2, key[1]);

                //query to database
                rs = pstmt.executeQuery();
                conn.commit(); //clear locks

                //loop through ResultSet gathering info
                while (rs.next()){
                     interests.add(new Interest( rs.getString("ministryName"),
                                                rs.getString("availability") ));
                }
                rs.close();
            }catch (SQLException e){
                System.err.println("Error in DBManager.getInterests() Families table: " + e);
            }
        }//elseif "familes"

        return interests;
    }//getInterets()

    /**
     * Gets ministry history for a family or an individual.
     * @param classification a String which specifies where to obtain info from.
     *                  The options are: "adults" or "dependents" or "families"
     * @param key an array String containing the attributes values
     *                  that make up a key for the given classification
     * @return A ArrayList containing History objects with all the history
     *                  information for an individual or family depending on
     *                  which one was asked for
     */
    public ArrayList<History> getHistories(String classification, String[] key){
        PreparedStatement pstmt;
        ResultSet rs = null;
        String prefix;
        String hSuffix = "History";
        String hQuery;
        ArrayList<History> histories = new ArrayList<History>();


        if (classification.equalsIgnoreCase("adults"))
            prefix = "a";
        else if (classification.equalsIgnoreCase("dependents"))
            prefix = "d";
        else //(table.equalsIgnoreCase("families"))
            prefix = "f";

        if (classification.equalsIgnoreCase("adults") || classification.equalsIgnoreCase("dependents")){
            try{
                //Get the ministry History
                hQuery = "SELECT * FROM "+prefix+hSuffix+" WHERE firstName=? " +
                        "AND lastName=? AND dob=? ORDER BY lastName";
                pstmt = conn.prepareStatement(hQuery);
                pstmt.setString(1, key[0]);
                pstmt.setString(2, key[1]);
                pstmt.setString(3, key[2]);

                //query to database
                rs = pstmt.executeQuery();
                conn.commit(); //clear locks

                //loop through ResultSet gathering info
                while (rs.next()){
                     histories.add(new History( rs.getString("ministryName"),
                                                rs.getString("date"),
                                                rs.getString("primaryPosition")));
                }
                rs.close();
            }catch (SQLException e){
                System.err.println("Error in DBManager.getHistories() Adults/Dependents table: " + e);
            }
        }else if (classification.equalsIgnoreCase("families")){
            try{
                //Get the ministry History
                hQuery = "SELECT * FROM "+prefix+hSuffix+" WHERE familyName=? " +
                        "AND primaryPhone=? ORDER BY familyName";
                pstmt = conn.prepareStatement(hQuery);
                pstmt.setString(1, key[0]);
                pstmt.setString(2, key[1]);

                //query to database
                rs = pstmt.executeQuery();
                conn.commit(); //clear locks

                //loop through ResultSet gathering info
                while (rs.next()){
                     histories.add(new History( rs.getString("ministryName"),
                                                rs.getString("date") ));
                }
                rs.close();
            }catch (SQLException e){
                System.err.println("Error in DBManager.getHistories() Families table: " + e);
            }
        }//elseif "families"

        return histories;
    }//getHistories()

    /**
     *
     * @param first a String containing the first name of an individual
     * @param last a String containing the last name of an individual
     * @param dob a String containing the date of birth of an individual
     * @return DBOject which will either be an Adult object, a Dependent
     *              object or null (if requested individual doesn't exist).
     */
    public DBObject getIndividual(String first, String last, String dob){
        ResultSet rs = null;
        PreparedStatement pstmt;
        DBObject person = null;
        //NOTE: cannot pass tables as bind variable using preparedstatements
        String query;

        //look in adults table
        try{
            query = "SELECT * FROM adults WHERE firstName=? AND lastName=? AND dob=?";
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, first);
            pstmt.setString(2, last);
            pstmt.setString(3, dob);

            //query to database
            rs = pstmt.executeQuery();
            conn.commit(); //clear locks

            //double check to make sure the ResultSet is not empty
            if (rs.next()){
                //rs.first();
                person = new Adult( rs.getString("firstName"), rs.getString("lastName"),
                                    rs.getString("dob"), rs.getString("dateJoined"),
                                    rs.getString("occupation"), rs.getString("employer"),
                                    rs.getString("workPhone"), rs.getString("personalPhone"),
                                    rs.getString("email"), rs.getString("notes") );
            }
            rs.close();
        }catch (SQLException e){
            System.err.println("Error in DBManager.getIndividual() Adult: " + e);
        }

        if (person == null){
            try{
                // look in dependents table
                query = "SELECT * FROM dependents WHERE firstName=? AND lastName=? AND dob=?";
                pstmt = conn.prepareStatement(query);
                pstmt.setString(1, first);
                pstmt.setString(2, last);
                pstmt.setString(3, dob);

                //query to database
                rs = pstmt.executeQuery();
                conn.commit(); //clear locks

                //double check to make sure the ResultSet is not empty
                if (rs.next()){
                    //rs.first();
                    person = new Dependent( rs.getString("firstName"), rs.getString("lastName"),
                                        rs.getString("dob"), rs.getString("school"),
                                        rs.getString("religiousSchooling"),
                                        rs.getString("personalPhone"),
                                        rs.getString("notes") );
                }
                rs.close();
                }catch (SQLException e){
                    System.err.println("Error in DBManager.getIndividual() Dependent: " + e);
                }
        }//if(person==null)

        return person;
    }//getIndividual()



//UPDATE METHODS
    /**
     * 
     * @param classification a String which specifies where to obtain info from.
     *                  The options are:
     *                  " " == none
     *                  "interest" or "finterest (family interest)
     *                  "history" or "fhistory" (family history)
     *                  "ainterest" (adult interest)
     *                  "ahistory" (adult history)
     *                  "dinterest" (dependent interest)
     *                  "dhistory" (dependent history)
     * @param record
     * @param personORfamilyKey
     * @return A boolean - true if the method was successful and false otherwise
     */
    public boolean updateRecord(String classification, DBObject oldRecord, DBObject newRecord, String[] personORfamilyKey){
        boolean didUpdate = true;

        try{
            if (oldRecord instanceof Adult){
                updateAdult((Adult) oldRecord, (Adult) newRecord, personORfamilyKey);
            }else if (oldRecord instanceof Dependent){
                updateDependent((Dependent)oldRecord, (Dependent) newRecord, personORfamilyKey);
            }else if (oldRecord instanceof Family){
                updateFamily((Family)oldRecord, (Family) newRecord);
            }else if (oldRecord instanceof Address){
                updateAddress((Address)oldRecord, (Address) newRecord, personORfamilyKey);
            }else if (oldRecord instanceof Money){
                updateMoney((Money) oldRecord, (Money) newRecord, personORfamilyKey);
            }else if (oldRecord instanceof Interest){
                if (classification.length()>8)  //"interest" == 7 characters
                    updateInterest(classification.charAt(0)+"", (Interest) oldRecord, (Interest) newRecord, personORfamilyKey);
                else
                    updateInterest("f", (Interest) oldRecord, (Interest) newRecord, personORfamilyKey);
            }else if (oldRecord instanceof History){
                if (classification.length()>7) //"history" == 7 characters
                    updateHistory(classification.charAt(0)+"", (History) oldRecord, (History) newRecord, personORfamilyKey);
                else
                    updateHistory("f", (History) oldRecord, (History) newRecord, personORfamilyKey);
            }

        }catch(SQLException e){
            didUpdate = false;
            System.err.println("Error in DBManager.updateRecord(): " + e);
            try {
              System.err.println("Transaction is being rolled back");
              conn.rollback();
            } catch(SQLException e2) {
              System.err.println("Rolling back transcation unsucessful");
            }
        }
        //DO NOT COMMIT!!

        return didUpdate;
    }//updateRecord()

    private void updateAdult(Adult oldRecord, Adult newRecord, String[] famKey) throws SQLException{
        String update;
        PreparedStatement pstmt;
        boolean samePrimaryKey = true;

        String[] oldKey = oldRecord.getKey();
        String[] newKey = newRecord.getKey();

        for(int i=0; i<oldKey.length; i++){
            if (!oldKey[i].equalsIgnoreCase(newKey[i])){
                //primary key has been changed and break out.
                samePrimaryKey = false;
                break;
            }//if
        }//for(i)

        if (samePrimaryKey){
            //update adults table
            update = "UPDATE adults "
                    + "SET dateJoined=?, occupation=?, employer=?, "
                    + "workphone=?, personalPhone=?, email=?, notes=? "
                    + "WHERE firstName=? AND lastName=? AND dob=?";
            pstmt = conn.prepareStatement(update);
            pstmt.setString(1, newRecord.getDateJoined());
            pstmt.setString(2, newRecord.getOccupation());
            pstmt.setString(3, newRecord.getEmployer());
            pstmt.setString(4, newRecord.getWorkPhoneNumber());
            pstmt.setString(5, newRecord.getPersonalPhoneNumber());
            pstmt.setString(6, newRecord.getEmailAddress());
            pstmt.setString(7, newRecord.getAdditionalNotes());
            pstmt.setString(8, oldKey[0]);
            pstmt.setString(9, oldKey[1]);
            pstmt.setString(10, oldKey[2]);
            pstmt.executeUpdate();
            pstmt.clearParameters();
        }else{
            //primary key has been changed
            Object[] attrib = newRecord.getAttributes();

            //add newRecord to Adults table
            update = "INSERT INTO adults VALUES(?,?,?,?,?,?,?,?,?,?)";
            pstmt = conn.prepareStatement(update);
            for (int j=0; j<attrib.length; j++){
                pstmt.setString(j+1, (String) attrib[j]);
            }//for(j)
            pstmt.executeUpdate();
            pstmt.clearParameters();

            //update every occurance of old key:
            //family_composed_of, in_relationship_with, lives_with, aInterest, aHistory
            update = "UPDATE family_composed_of "
                    + "SET firstName=?, lastName=?, dob=? "
                    + "WHERE firstName=? AND lastName=? AND dob=?";
            pstmt = conn.prepareStatement(update);
            pstmt.setString(1, newKey[0]);
            pstmt.setString(2, newKey[1]);
            pstmt.setString(3, newKey[2]);
            pstmt.setString(4, oldKey[0]);
            pstmt.setString(5, oldKey[1]);
            pstmt.setString(6, oldKey[2]);
            pstmt.executeUpdate();
            pstmt.clearParameters();

            update = "UPDATE in_relationship_with "
                    + "SET firstName1=?, lastName1=?, dob1=? "
                    + "WHERE firstName1=? AND lastName1=? AND dob1=?";
            pstmt = conn.prepareStatement(update);
            pstmt.setString(1, newKey[0]);
            pstmt.setString(2, newKey[1]);
            pstmt.setString(3, newKey[2]);
            pstmt.setString(4, oldKey[0]);
            pstmt.setString(5, oldKey[1]);
            pstmt.setString(6, oldKey[2]);
            pstmt.executeUpdate();
            pstmt.clearParameters();

            update = "UPDATE in_relationship_with "
                    + "SET firstName2=?, lastName2=?, dob2=? "
                    + "WHERE firstName2=? AND lastName2=? AND dob2=?";
            pstmt = conn.prepareStatement(update);
            pstmt.setString(1, newKey[0]);
            pstmt.setString(2, newKey[1]);
            pstmt.setString(3, newKey[2]);
            pstmt.setString(4, oldKey[0]);
            pstmt.setString(5, oldKey[1]);
            pstmt.setString(6, oldKey[2]);
            pstmt.executeUpdate();
            pstmt.clearParameters();

            update = "UPDATE lives_with "
                    + "SET afirstName=?, alastName=?, aDOB=? "
                    + "WHERE afirstName=? AND alastName=? AND aDOB=?";
            pstmt = conn.prepareStatement(update);
            pstmt.setString(1, newKey[0]);
            pstmt.setString(2, newKey[1]);
            pstmt.setString(3, newKey[2]);
            pstmt.setString(4, oldKey[0]);
            pstmt.setString(5, oldKey[1]);
            pstmt.setString(6, oldKey[2]);
            pstmt.executeUpdate();
            pstmt.clearParameters();

            update = "UPDATE aInterest "
                    + "SET firstName=?, lastName=?, dob=? "
                    + "WHERE firstName=? AND lastName=? AND dob=?";
            pstmt = conn.prepareStatement(update);
            pstmt.setString(1, newKey[0]);
            pstmt.setString(2, newKey[1]);
            pstmt.setString(3, newKey[2]);
            pstmt.setString(4, oldKey[0]);
            pstmt.setString(5, oldKey[1]);
            pstmt.setString(6, oldKey[2]);
            pstmt.executeUpdate();
            pstmt.clearParameters();

            update = "UPDATE aHistory "
                    + "SET firstName=?, lastName=?, dob=? "
                    + "WHERE firstName=? AND lastName=? AND dob=?";
            pstmt = conn.prepareStatement(update);
            pstmt.setString(1, newKey[0]);
            pstmt.setString(2, newKey[1]);
            pstmt.setString(3, newKey[2]);
            pstmt.setString(4, oldKey[0]);
            pstmt.setString(5, oldKey[1]);
            pstmt.setString(6, oldKey[2]);
            pstmt.executeUpdate();
            pstmt.clearParameters();

            //delete oldKey from Adults table
            update = "DELETE FROM adults "
                + "WHERE firstName=? AND lastName=? AND dob=?";
            pstmt = conn.prepareStatement(update);
            pstmt.setString(1, oldKey[0]);
            pstmt.setString(2, oldKey[1]);
            pstmt.setString(3, oldKey[2]);
            pstmt.executeUpdate();
            pstmt.clearParameters();
        }
        //DO NOT COMMIT!!
    }//updateAdult()

    private void updateDependent(Dependent oldRecord, Dependent newRecord, String[] famKey) throws SQLException{
        String update;
        PreparedStatement pstmt;
        boolean samePrimaryKey = true;

        String[] oldKey = oldRecord.getKey();
        String[] newKey = newRecord.getKey();

        for(int i=0; i<oldKey.length; i++){
            if (!oldKey[i].equalsIgnoreCase(newKey[i])){
                //primary key has been changed and break out.
                samePrimaryKey = false;
                break;
            }//if
        }//for(i)

        if (samePrimaryKey){
            //update dependents table
            update = "UPDATE dependents "
                    + "SET school=?, religiousSchooling=?, "
                    + "personalPhone=?, notes=? "
                    + "WHERE firstName=? AND lastName=? AND dob=?";
            pstmt = conn.prepareStatement(update);
            pstmt.setString(1, newRecord.getSchoolAttending());
            pstmt.setString(2, newRecord.getReligiousSchooling());
            pstmt.setString(3, newRecord.getPersonalPhoneNumber());
            pstmt.setString(4, newRecord.getAdditionalNotes());
            pstmt.setString(5, oldKey[0]);
            pstmt.setString(6, oldKey[1]);
            pstmt.setString(7, oldKey[2]);
            pstmt.executeUpdate();
            pstmt.clearParameters();
            
        }else{
            //primary key has been changed
            Object[] attrib = newRecord.getAttributes();

            //add newRecord to Dependents table
            update = "INSERT INTO dependents VALUES(?,?,?,?,?,?,?)";
            pstmt = conn.prepareStatement(update);
            for (int j=0; j<attrib.length; j++){
                pstmt.setString(j+1, (String) attrib[j]);
            }//for(j)
            pstmt.executeUpdate();
            pstmt.clearParameters();

            //update every occurance of old key:
            //family_composed_of, lives_with, dInterest, dHistory
            update = "UPDATE family_composed_of "
                    + "SET firstName=?, lastName=?, dob=? "
                    + "WHERE firstName=? AND lastName=? AND dob=?";
            pstmt = conn.prepareStatement(update);
            pstmt.setString(1, newKey[0]);
            pstmt.setString(2, newKey[1]);
            pstmt.setString(3, newKey[2]);
            pstmt.setString(4, oldKey[0]);
            pstmt.setString(5, oldKey[1]);
            pstmt.setString(6, oldKey[2]);
            pstmt.executeUpdate();
            pstmt.clearParameters();

            update = "UPDATE lives_with "
                    + "SET dfirstName=?, dlastName=?, dDOB=? "
                    + "WHERE dfirstName=? AND dlastName=? AND dDOB=?";
            pstmt = conn.prepareStatement(update);
            pstmt.setString(1, newKey[0]);
            pstmt.setString(2, newKey[1]);
            pstmt.setString(3, newKey[2]);
            pstmt.setString(4, oldKey[0]);
            pstmt.setString(5, oldKey[1]);
            pstmt.setString(6, oldKey[2]);
            pstmt.executeUpdate();
            pstmt.clearParameters();

            update = "UPDATE dInterest "
                    + "SET firstName=?, lastName=?, dob=? "
                    + "WHERE firstName=? AND lastName=? AND dob=?";
            pstmt = conn.prepareStatement(update);
            pstmt.setString(1, newKey[0]);
            pstmt.setString(2, newKey[1]);
            pstmt.setString(3, newKey[2]);
            pstmt.setString(4, oldKey[0]);
            pstmt.setString(5, oldKey[1]);
            pstmt.setString(6, oldKey[2]);
            pstmt.executeUpdate();
            pstmt.clearParameters();

            update = "UPDATE dHistory "
                    + "SET firstName=?, lastName=?, dob=? "
                    + "WHERE firstName=? AND lastName=? AND dob=?";
            pstmt = conn.prepareStatement(update);
            pstmt.setString(1, newKey[0]);
            pstmt.setString(2, newKey[1]);
            pstmt.setString(3, newKey[2]);
            pstmt.setString(4, oldKey[0]);
            pstmt.setString(5, oldKey[1]);
            pstmt.setString(6, oldKey[2]);
            pstmt.executeUpdate();
            pstmt.clearParameters();

            //delete oldKey from Dependents table
            update = "DELETE FROM dependents "
                + "WHERE firstName=? AND lastName=? AND dob=?";
            pstmt = conn.prepareStatement(update);
            pstmt.setString(1, oldKey[0]);
            pstmt.setString(2, oldKey[1]);
            pstmt.setString(3, oldKey[2]);
            pstmt.executeUpdate();
            pstmt.clearParameters();

        }
        //DO NOT COMMIT!!
    }//updateDependent()

    private void updateFamily(Family oldRecord, Family newRecord) throws SQLException{
        String update;
        PreparedStatement pstmt;
        boolean samePrimaryKey = true;

        String[] oldKey = oldRecord.getKey();
        String[] newKey = newRecord.getKey();

        for(int i=0; i<oldKey.length; i++){
            if (!oldKey[i].equalsIgnoreCase(newKey[i])){
                //primary key has been changed and break out.
                samePrimaryKey = false;
                break;
            }//if
        }//for(i)

        if (samePrimaryKey){
            //update families table
            update = "UPDATE families "
                    + "SET headOfHouse=?, sendDonateLetter=?, "
                    + "sendMinistryLetter=?, salutation=?, notes=? "
                    + "WHERE familyName=? AND primaryPhone=?";
            pstmt = conn.prepareStatement(update);
            pstmt.setString(1, newRecord.getHeadOfHousehold());
            pstmt.setBoolean(2, newRecord.getSendtDonationLetter());
            pstmt.setBoolean(3, newRecord.getSendMinistryLetter());
            pstmt.setString(4, newRecord.getLetterSalutation());
            pstmt.setString(5, newRecord.getAdditionalNotes());
            pstmt.setString(6, oldKey[0]);
            pstmt.setString(7, oldKey[1]);
            pstmt.executeUpdate();
            pstmt.clearParameters();

        }else{
            //primary key has been changed
            Object[] fAtts = newRecord.getAttributes();

            //add newRecord to Dependents table
            update = "INSERT INTO families VALUES(?,?,?,?,?,?,?)";
            pstmt = conn.prepareStatement(update);
            pstmt.setString(1, (String) fAtts[0]);
            pstmt.setString(2, (String) fAtts[1]);
            pstmt.setString(3, (String) fAtts[2]);
            pstmt.setBoolean(4, (Boolean) fAtts[3]);
            pstmt.setBoolean(5, (Boolean) fAtts[4]);
            pstmt.setString(6, (String) fAtts[5]);
            pstmt.setString(7, (String) fAtts[6]);
            pstmt.executeUpdate();
            pstmt.clearParameters();

            //update every occurance of old key:
            //family_composed_of, fam_has_address fInterest, fHistory
            update = "UPDATE family_composed_of "
                    + "SET familyName=?, primaryPhone=? "
                    + "WHERE familyName=? AND primaryPhone=?";
            pstmt = conn.prepareStatement(update);
            pstmt.setString(1, newKey[0]);
            pstmt.setString(2, newKey[1]);
            pstmt.setString(3, oldKey[0]);
            pstmt.setString(4, oldKey[1]);
            pstmt.executeUpdate();
            pstmt.clearParameters();

            update = "UPDATE fam_has_address "
                    + "SET familyName=?, primaryPhone=? "
                    + "WHERE familyName=? AND primaryPhone=?";
            pstmt = conn.prepareStatement(update);
            pstmt.setString(1, newKey[0]);
            pstmt.setString(2, newKey[1]);
            pstmt.setString(3, oldKey[0]);
            pstmt.setString(4, oldKey[1]);
            pstmt.executeUpdate();
            pstmt.clearParameters();

            update = "UPDATE money "
                    + "SET familyName=?, primaryPhone=? "
                    + "WHERE familyName=? AND primaryPhone=?";
            pstmt = conn.prepareStatement(update);
            pstmt.setString(1, newKey[0]);
            pstmt.setString(2, newKey[1]);
            pstmt.setString(3, oldKey[0]);
            pstmt.setString(4, oldKey[1]);
            pstmt.executeUpdate();
            pstmt.clearParameters();

            update = "UPDATE fInterest "
                    + "SET familyName=?, primaryPhone=? "
                    + "WHERE familyName=? AND primaryPhone=?";
            pstmt = conn.prepareStatement(update);
            pstmt.setString(1, newKey[0]);
            pstmt.setString(2, newKey[1]);
            pstmt.setString(3, oldKey[0]);
            pstmt.setString(4, oldKey[1]);
            pstmt.executeUpdate();
            pstmt.clearParameters();

            update = "UPDATE fHistory "
                    + "SET familyName=?, primaryPhone=? "
                    + "WHERE familyName=? AND primaryPhone=?";
            pstmt = conn.prepareStatement(update);
            pstmt.setString(1, newKey[0]);
            pstmt.setString(2, newKey[1]);
            pstmt.setString(3, oldKey[0]);
            pstmt.setString(4, oldKey[1]);
            pstmt.executeUpdate();
            pstmt.clearParameters();

            //delete oldKey from Families table
            update = "DELETE FROM families "
                    + "WHERE familyName=? AND primaryPhone=?";
            pstmt = conn.prepareStatement(update);
            pstmt.setString(1, oldKey[0]);
            pstmt.setString(2, oldKey[1]);
            pstmt.executeUpdate();
            pstmt.clearParameters();
        }
        //DO NOT COMMIT!!
    }//updateFamily()

    private void updateAddress(Address oldRecord, Address newRecord, String[] famKey) throws SQLException{
        String update;
        String insert;
        String delete;
        PreparedStatement pstmt;
        boolean samePrimaryKey = true;

        String[] oldKey = oldRecord.getKey();
        String[] newKey = newRecord.getKey();

        for(int i=0; i<oldKey.length; i++){
            if (!oldKey[i].equalsIgnoreCase(newKey[i])){
                //primary key has been changed and break out.
                samePrimaryKey = false;
                break;
            }//if
        }//for(i)

        if (samePrimaryKey){
            //update only the Address table (families can not share addresses)
            update = "UPDATE address "
                    + "SET municipal=?, isCurrentMailing=?, notes=? "
                    + "WHERE street=? AND zipcode=?";
            pstmt = conn.prepareStatement(update);
            pstmt.setString(1, newRecord.getMunicipal());
            pstmt.setBoolean(2, newRecord.getIsCurrentMailingAddress());
            pstmt.setString(3, newRecord.getAdditionalNotes());
            pstmt.setString(4, oldKey[0]);
            pstmt.setString(5, oldKey[1]);
            pstmt.executeUpdate();
            pstmt.clearParameters();
            
        }else{
            //primary key has been changed
            //add new record to city_state table if needed
            try{
                insert = "INSERT INTO city_state "
                        + "VALUES(?,?,?)";
                pstmt = conn.prepareStatement(insert);
                pstmt.setString(1, newRecord.getZipcode());
                pstmt.setString(2, newRecord.getCityName());
                pstmt.setString(2, newRecord.getStateName());
                pstmt.executeUpdate();
                pstmt.clearParameters();
            }
            catch(Exception e){
                //do nothing, just wanted to ensure city_state record exists
            }

            //add new record to address table
            insert = "INSERT INTO address VALUES(?,?,?,?,?)";
            pstmt = conn.prepareStatement(insert);
            pstmt.setString(1, newRecord.getStreet());                      //street
            pstmt.setString(2, newRecord.getZipcode());                     //zipcode
            pstmt.setString(3, newRecord.getMunicipal());                   //municipal
            pstmt.setBoolean(4, newRecord.getIsCurrentMailingAddress());    //isCurrentMailing
            pstmt.setString(5, newRecord.getAdditionalNotes());             //notes
            pstmt.executeUpdate();
            pstmt.clearParameters();

            //update fam_has_address table
            update = "UPDATE fam_has_address "
                    + "SET street=?, zipcode=? "
                    + "WHERE familyName=? AND primaryPhone=? AND street=? AND zipcode=?";
            pstmt = conn.prepareStatement(update);
            pstmt.setString(1, newRecord.getStreet());
            pstmt.setString(2, newRecord.getZipcode());
            pstmt.setString(3, famKey[0]);
            pstmt.setString(4, famKey[1]);
            pstmt.setString(5, oldRecord.getStreet());
            pstmt.setString(6, oldRecord.getZipcode());
            pstmt.executeUpdate();
            pstmt.clearParameters();

            //delete instance of old record key including family key
            delete = "DELETE FROM address "
                    + "WHERE street=? AND zipcode=?";
            pstmt = conn.prepareStatement(delete);
            pstmt.setString(1, oldRecord.getStreet());
            pstmt.setString(2, oldRecord.getZipcode());
            pstmt.executeUpdate();
            pstmt.clearParameters();

        }
        //DO NOT COMMIT!!
    }//updateAddress()

    private void updateMoney(Money oldRecord, Money newRecord, String[] famKey) throws SQLException{
        String update;
        String insert;
        String delete;
        PreparedStatement pstmt;
        boolean samePrimaryKey = true;

        String[] oldKey = oldRecord.getKey();
        String[] newKey = newRecord.getKey();

        for(int i=0; i<oldKey.length; i++){
            if (!oldKey[i].equalsIgnoreCase(newKey[i])){
                //primary key has been changed and break out.
                samePrimaryKey = false;
                break;
            }//if
        }//for(i)
        
        if (samePrimaryKey){
            //update Money table
            update = "UPDATE money "
                    + "SET pledged=?, year2date=?, notes=? "
                    + "WHERE theYear=? AND familyName=? AND primaryPhone=?";
            pstmt = conn.prepareStatement(update);
            pstmt.setDouble(1, newRecord.getPledged());
            pstmt.setDouble(2, newRecord.getYear2Date());
            pstmt.setString(3, newRecord.getAdditionalNotes());
            pstmt.setString(4, oldKey[0]);
            pstmt.setString(5, famKey[0]);
            pstmt.setString(6, famKey[1]);
            pstmt.executeUpdate();
            pstmt.clearParameters();

        }else{
            //primary key has been changed
            //add new record to moeny table
            insert = "INSERT INTO money "
                        + "VALUES(?,?,?,?,?,?) ";
            pstmt = conn.prepareStatement(insert);
            pstmt.setString(1, newRecord.getYear());
            pstmt.setString(2, famKey[0]);
            pstmt.setString(3, famKey[1]);
            pstmt.setDouble(4, newRecord.getPledged());
            pstmt.setDouble(5, newRecord.getYear2Date());
            pstmt.setString(6, newRecord.getAdditionalNotes());
            pstmt.executeUpdate();
            pstmt.clearParameters();

            //delete instance of old record key including family key
            delete = "DELETE FROM money " +
                 "WHERE familyName=? AND primaryPhone=? AND "
                 + "theYear=?";
            pstmt = conn.prepareStatement(delete);
            pstmt.setString(1, famKey[0]);
            pstmt.setString(2, famKey[1]);
            pstmt.setString(3, oldKey[0]);
            pstmt.executeUpdate();
            pstmt.clearParameters();
        }
        //DO NOT COMMIT!!
    }//updateMoney()

    private void updateInterest(String prefix, Interest oldRecord, Interest newRecord, String[] personORfamKey) throws SQLException{
        String update;
        String insert;
        String delete;
        PreparedStatement pstmt;
        boolean samePrimaryKey = true;

        String[] oldKey = oldRecord.getKey();
        String[] newKey = newRecord.getKey();

        for(int i=0; i<oldKey.length; i++){
            if (!oldKey[i].equalsIgnoreCase(newKey[i])){
                //primary key has been changed and break out.
                samePrimaryKey = false;
                break;
            }//if
        }//for(i)

        if (samePrimaryKey){
            if(prefix.equalsIgnoreCase("f")){
                 //update fInterest table
               update = "UPDATE "+prefix+"interest "
                        + "SET availability=? "
                        + "WHERE ministryName=? AND "
                        + "familyName=? AND primaryPhone=?";
                pstmt = conn.prepareStatement(update);
                pstmt.setString(1, newRecord.getAvailability());
                pstmt.setString(2, oldKey[0]);
                pstmt.setString(3, personORfamKey[0]);
                pstmt.setString(4, personORfamKey[1]);
                pstmt.executeUpdate();
                pstmt.clearParameters();
            }else{
                //update dInterest or aInterest table
               update = "UPDATE "+prefix+"interest "
                        + "SET availability=? "
                        + "WHERE ministryName=? AND "
                        + "firstName=? AND lastName=? AND dob=?";
                pstmt = conn.prepareStatement(update);
                pstmt.setString(1, newRecord.getAvailability());
                pstmt.setString(2, oldKey[0]);
                pstmt.setString(3, personORfamKey[0]);
                pstmt.setString(4, personORfamKey[1]);
                pstmt.setString(5, personORfamKey[2]);
                pstmt.executeUpdate();
                pstmt.clearParameters();
            }//else
        }else{
            //primary key has been changed
            if(prefix.equalsIgnoreCase("f")){
                //add newRecord
                insert = "INSERT INTO "+prefix+"interest "
                        + "VALUES(?,?,?,?) ";
                pstmt = conn.prepareStatement(insert);
                pstmt.setString(1, personORfamKey[0]);
                pstmt.setString(2, personORfamKey[1]);
                pstmt.setString(3, newKey[0]);
                pstmt.setString(4, newRecord.getAvailability());
                pstmt.executeUpdate();
                pstmt.clearParameters();

                //delete instance of oldRecord
                if (oldRecord.getAvailability() != null){
                    delete = "DELETE FROM "+prefix+"interest " +
                         "WHERE familyName=? AND primaryPhone=? AND "
                         + "ministryName=? AND availability=?";
                    pstmt = conn.prepareStatement(delete);
                    pstmt.setString(1, personORfamKey[0]);
                    pstmt.setString(2, personORfamKey[1]);
                    pstmt.setString(3, oldKey[0]);
                    pstmt.setString(4, oldRecord.getAvailability());
                    pstmt.executeUpdate();
                    pstmt.clearParameters();
                }else{
                    delete = "DELETE FROM "+prefix+"interest " +
                         "WHERE familyName=? AND primaryPhone=? AND "
                         + "ministryName=?";
                    pstmt = conn.prepareStatement(delete);
                    pstmt.setString(1, personORfamKey[0]);
                    pstmt.setString(2, personORfamKey[1]);
                    pstmt.setString(3, oldKey[0]);
                    pstmt.executeUpdate();
                    pstmt.clearParameters();
                }
            }else{ //else "a" or "d"
                //primary key has been changed
                //add newRecord
                insert = "INSERT INTO "+prefix+"interest "
                        + "VALUES(?,?,?,?,?) ";
                pstmt = conn.prepareStatement(insert);
                pstmt.setString(1, personORfamKey[0]);
                pstmt.setString(2, personORfamKey[1]);
                pstmt.setString(3, personORfamKey[2]);
                pstmt.setString(4, newKey[0]);
                pstmt.setString(5, newRecord.getAvailability());
                pstmt.executeUpdate();
                pstmt.clearParameters();

                //delete instance of oldRecord
                if (oldRecord.getAvailability() != null){
                    delete = "DELETE FROM "+prefix+"interest " +
                         "WHERE firstName=? AND lastName=? AND dob=? AND "
                         + "ministryName=? AND availability=?";
                    pstmt = conn.prepareStatement(delete);
                    pstmt.setString(1, personORfamKey[0]);
                    pstmt.setString(2, personORfamKey[1]);
                    pstmt.setString(3, personORfamKey[2]);
                    pstmt.setString(4, oldKey[0]);
                    pstmt.setString(5, oldRecord.getAvailability());
                    pstmt.executeUpdate();
                    pstmt.clearParameters();
                }else{
                    delete = "DELETE FROM "+prefix+"interest " +
                         "WHERE firstName=? AND lastName=? AND dob=? AND "
                         + "ministryName=?";
                    pstmt = conn.prepareStatement(delete);
                    pstmt.setString(1, personORfamKey[0]);
                    pstmt.setString(2, personORfamKey[1]);
                    pstmt.setString(3, personORfamKey[2]);
                    pstmt.setString(4, oldKey[0]);
                    pstmt.executeUpdate();
                    pstmt.clearParameters();
                }
            }//else
        }
        //DO NOT COMMIT!!
    }//uupdateInterest()

    private void updateHistory(String prefix, History oldRecord, History newRecord, String[] personORfamKey) throws SQLException{
        String update;
        String insert;
        String delete;
        PreparedStatement pstmt;
        boolean samePrimaryKey = true;

        String[] oldKey = oldRecord.getKey();
        String[] newKey = newRecord.getKey();

        for(int i=0; i<oldKey.length; i++){
            if (!oldKey[i].equalsIgnoreCase(newKey[i])){
                //primary key has been changed and break out.
                samePrimaryKey = false;
                break;
            }//if
        }//for(i)

        if (samePrimaryKey){
            //no need to update fHistory because all attributes are part of the
            //primary key and if it didn't change there is nothing to update

            if(prefix.equalsIgnoreCase("d") || prefix.equalsIgnoreCase("a")){
                //update dHistory or aHistory table
               update = "UPDATE "+prefix+"history "
                        + "SET primaryPosition=? "
                        + "WHERE ministryName=? AND date=? AND "
                        + "firstName=? AND lastName=? AND dob=?";
                pstmt = conn.prepareStatement(update);
                pstmt.setString(1, newRecord.getPrimaryPosition());
                pstmt.setString(2, oldKey[0]);
                pstmt.setString(3, oldKey[1]);
                pstmt.setString(4, personORfamKey[0]);
                pstmt.setString(5, personORfamKey[1]);
                pstmt.setString(6, personORfamKey[2]);
                pstmt.executeUpdate();
                pstmt.clearParameters();
            }
        }else{
            if (prefix.equalsIgnoreCase("f")){
                //primary key has been changed because all attributes are part of the primary key
                //add newRecord
                insert = "INSERT INTO "+prefix+"history "
                        + "VALUES(?,?,?,?) ";
                pstmt = conn.prepareStatement(insert);
                pstmt.setString(1, personORfamKey[0]);
                pstmt.setString(2, personORfamKey[1]);
                pstmt.setString(3, newKey[0]);
                pstmt.setString(4, newKey[1]);
                pstmt.executeUpdate();
                pstmt.clearParameters();

                //delete instance of oldRecord
                delete = "DELETE FROM "+prefix+"history " +
                     "WHERE familyName=? AND primaryPhone=? AND "
                     + "ministryName=? AND date=?";
                pstmt = conn.prepareStatement(delete);
                pstmt.setString(1, personORfamKey[0]);
                pstmt.setString(2, personORfamKey[1]);
                pstmt.setString(3, oldKey[0]);
                pstmt.setString(4, oldKey[1]);
                pstmt.executeUpdate();
                pstmt.clearParameters();

            }else{//prefix "d" or "a"
                //primary key has been changed
                //add newRecord
                insert = "INSERT INTO "+prefix+"history "
                        + "VALUES(?,?,?,?,?,?) ";
                pstmt = conn.prepareStatement(insert);
                pstmt.setString(1, personORfamKey[0]);
                pstmt.setString(2, personORfamKey[1]);
                pstmt.setString(3, personORfamKey[2]);
                pstmt.setString(4, newKey[0]);
                pstmt.setString(5, newKey[1]);
                pstmt.setString(6, newRecord.getPrimaryPosition());
                pstmt.executeUpdate();
                pstmt.clearParameters();

                //delete instance of oldRecord
                if (oldRecord.getPrimaryPosition() != null){
                    delete = "DELETE FROM "+prefix+"history " +
                         "WHERE firstName=? AND lastName=? AND dob=? AND "
                         + "ministryName=? AND date=? AND primaryPosition=?";
                    pstmt = conn.prepareStatement(delete);
                    pstmt.setString(1, personORfamKey[0]);
                    pstmt.setString(2, personORfamKey[1]);
                    pstmt.setString(3, personORfamKey[2]);
                    pstmt.setString(4, oldKey[0]);
                    pstmt.setString(5, oldKey[1]);
                    pstmt.setString(6, oldRecord.getPrimaryPosition());
                    pstmt.executeUpdate();
                    pstmt.clearParameters();
                }else{
                    delete = "DELETE FROM "+prefix+"history " +
                         "WHERE firstName=? AND lastName=? AND dob=? AND "
                         + "ministryName=? AND date=?";
                    pstmt = conn.prepareStatement(delete);
                    pstmt.setString(1, personORfamKey[0]);
                    pstmt.setString(2, personORfamKey[1]);
                    pstmt.setString(3, personORfamKey[2]);
                    pstmt.setString(4, oldKey[0]);
                    pstmt.setString(5, oldKey[1]);
                    pstmt.executeUpdate();
                    pstmt.clearParameters();
                }
            }
        }//else !samePrimaryKey

        //DO NOT COMMIT!!
    }//updateHistory()
    

    
//DELETE METHODS
    /**
     * This method does not commit changes to database.
     * @param classification a String which specifies where to obtain info from.
     *                  The options are:
     *                  "adults"
     *                  "dependents"
     *                  "address"
     *                  "money"
     *                  "interest" or "finterest (family interest)
     *                  "history" or "fhistory" (family history)
     *                  "ainterest" (adult interest)
     *                  "ahistory" (adult history)
     *                  "dinterest" (dependent interest)
     *                  "dhistory" (dependent history)
     * @param personORfamilyKey
     * @param familyKey
     * @return A boolean - true if the method was successful and false otherwise
     */
    public boolean deleteRecord(String classification, String[] recordKey,
                                    String[] personORfamilyKey){
        boolean didDelete = true;
        String prefix = "f";
        classification = classification.toLowerCase();

        if (classification.contains("interest")){
            //interest == 8 characters
            if (classification.length()>8)
                prefix = classification.charAt(0) + "";
        }else if (classification.contains("history")){
            //history == 7 characters
            if (classification.length()>7)
                prefix = classification.charAt(0) + "";
        }//prefix

        try{
            if (classification.equals("adults") ){
                deleteAdult(recordKey);
            } else if (classification.equals("dependents")){
                deleteDependent(recordKey, personORfamilyKey);
            } else if (classification.equals("address")){
                deleteAddress(recordKey);
            } else if (classification.equals("money")){
                deleteMoney(recordKey, personORfamilyKey);
            } else if (classification.contains("interest")){
                deleteInterest(prefix, recordKey, personORfamilyKey);
            }else if (classification.contains("history")){
                deleteHistory(prefix, recordKey, personORfamilyKey);
            }

        }catch(SQLException e){
            didDelete = false;
            System.err.println("Error in DBManager.deleteRecord(): " + e);
            try {
              System.err.println("Transaction is being rolled back");
              conn.rollback();
            } catch(SQLException e2) {
              System.err.println("Rolling back transcation unsucessful");
            }
        }
        //DO NOT COMMIT!!
        return didDelete;
    }//deleteRecord()

    /**
     * 
     * @param adults
     * @param dependents
     * @return A boolean - true if the method was successful and false otherwise
     */
    public boolean deleteHousehold(ArrayList<Adult> adults, ArrayList<Dependent> dependents,
                                    Family family, ArrayList<Address> addresses,
                                    ArrayList<Money> monies, ArrayList<Interest> interests,
                                    ArrayList<History> histories){
        boolean didCommit = false;
        String[] fKey = family.getKey();
       
        try{
            //Start backwards
            for(History h : histories)
                deleteHistory("f", h.getKey(), fKey);
           
            for(Interest i : interests)
                deleteInterest("f", i.getKey(), fKey);

            for(Money m : monies)
                deleteMoney(m.getKey(), fKey);

            for(Address a : addresses)
                deleteAddress(a.getKey());
 
            deleteFamily(fKey);

            for(Dependent d : dependents)
                deleteDependent(d.getKey(), fKey);

            for(Adult a : adults)
                deleteAdult(a.getKey());
            
            //Commit to database
            conn.commit();
            didCommit = true;
        }catch (SQLException e){
            System.err.println("Error in DBManager.deleteHousehold(): " + e);
            try {
              System.err.println("Transaction is being rolled back");
              conn.rollback();
            } catch(SQLException e2) {
              System.err.println("Rolling back transcation unsucessful");
            }
        }

        return didCommit;
    }//deleteHousehold()


    /**
     * Deletes Adult from the database. First from the aInterest table,
     *          then aHistory table, then Family_Composed_Of
     *          table, then Lives_with table, then In_Relationship_With
     *          table and then from the Adults table
     * @param classification
     * @param key
     */
    private void deleteAdult(String[] key) throws SQLException{
        String delete;
        PreparedStatement pstmt;

        delete = "DELETE FROM aInterest "
                + "WHERE firstName=? AND lastName=? AND dob=?";
        pstmt = conn.prepareStatement(delete);
        pstmt.setString(1, key[0]);
        pstmt.setString(2, key[1]);
        pstmt.setString(3, key[2]);
        pstmt.executeUpdate();
        pstmt.clearParameters();

        delete = "DELETE FROM aHistory "
                + "WHERE firstName=? AND lastName=? AND dob=?";
        pstmt = conn.prepareStatement(delete);
        pstmt.setString(1, key[0]);
        pstmt.setString(2, key[1]);
        pstmt.setString(3, key[2]);
        pstmt.executeUpdate();
        pstmt.clearParameters();

        delete = "DELETE FROM family_composed_of "
                + "WHERE firstName=? AND lastName=? AND dob=?";
        pstmt = conn.prepareStatement(delete);
        pstmt.setString(1, key[0]);
        pstmt.setString(2, key[1]);
        pstmt.setString(3, key[2]);
        pstmt.executeUpdate();
        pstmt.clearParameters();

        delete = "DELETE FROM lives_with "
                + "WHERE aFirstName=? AND aLastName=? AND aDOB=?";
        pstmt = conn.prepareStatement(delete);
        pstmt.setString(1, key[0]);
        pstmt.setString(2, key[1]);
        pstmt.setString(3, key[2]);
        pstmt.executeUpdate();
        pstmt.clearParameters();

        delete = "DELETE FROM in_relationship_with WHERE (firstName1=? AND lastName1=? AND dob1=?)"+
                "OR (firstName2=? AND lastName2=? AND dob2=?)";
        pstmt = conn.prepareStatement(delete);
        pstmt.setString(1, key[0]);
        pstmt.setString(2, key[1]);
        pstmt.setString(3, key[2]);
        pstmt.setString(4, key[0]);
        pstmt.setString(5, key[1]);
        pstmt.setString(6, key[2]);
        pstmt.executeUpdate();
        pstmt.clearParameters();

        delete = "DELETE FROM adults "
                + "WHERE firstName=? AND lastName=? AND dob=?";
        pstmt = conn.prepareStatement(delete);
        pstmt.setString(1, key[0]);
        pstmt.setString(2, key[1]);
        pstmt.setString(3, key[2]);
        pstmt.executeUpdate();
        pstmt.clearParameters();

        //DO NOT COMMIT!
    }//deleteAdult()

    /**
     * Deletes Dependent from the database. First from the dInterest table,
     *          then dHistory table, then Family_Composed_Of
     *          table, then Lives_with table, then In_Relationship_With
     *          table and then from the Adults table
     * @param key
     * @param famKey
     */
    private void deleteDependent(String[] key, String[] famKey) throws SQLException{
        String delete;
        String query;
        PreparedStatement pstmt;
        ResultSet rs;
        ArrayList<String[]> adults = new ArrayList<String[]>();
        String[] aKey = new String[3];

        //check to see if dependent exists in another family
        query = "SELECT firstName, lastName, dob "
                + "FROM family_composed_of "
                + "WHERE firstName=? AND lastName=? AND dob=? AND "
                + "familyName<>? AND primaryPhone<>?";

        pstmt = conn.prepareStatement(query);
        pstmt.setString(1, key[0]);
        pstmt.setString(2, key[1]);
        pstmt.setString(3, key[2]);
        pstmt.setString(4, famKey[0]);
        pstmt.setString(5, famKey[1]);
        rs = pstmt.executeQuery();
        pstmt.clearParameters();

        if (rs.next()){
            //Dependent is shared! Will not be deleteing dependent, just association to family
            //gather other adults assoicated with dependent
            do{
                aKey[0] = rs.getString(1);
                aKey[0] = rs.getString(1);
                aKey[0] = rs.getString(1);
                adults.add(aKey);

                aKey = new String[3];
            }while(rs.next());

            //delete out of the family_composed_of table
            delete = "DELETE FROM family_composed_of "
                    + "WHERE firstName=? AND lastName=? AND dob=? AND "
                    + "familyName=? AND primaryPhone=?";
            pstmt = conn.prepareStatement(delete);
            pstmt.setString(1, key[0]);
            pstmt.setString(2, key[1]);
            pstmt.setString(3, key[2]);
            pstmt.setString(4, famKey[0]);
            pstmt.setString(5, famKey[1]);
            pstmt.executeUpdate();
            pstmt.clearParameters();

            //delete out of the lives_with table where dep and adults
            delete = "DELETE FROM lives_with "
                    + "WHERE dFirstName=? AND dLastName=? AND dDOB=? AND "
                    + "aFirstName=? AND aLastName=? AND aDOB=?";
            pstmt = conn.prepareStatement(delete);
            for (String[] a : adults){
                pstmt.setString(1, key[0]);
                pstmt.setString(2, key[1]);
                pstmt.setString(3, key[2]);
                pstmt.setString(4, a[0]);
                pstmt.setString(5, a[1]);
                pstmt.setString(6, a[2]);
                pstmt.executeUpdate();
                pstmt.clearParameters();
            }//for each string[] in adults

        }else{
            delete = "DELETE FROM dInterest "
                    + "WHERE firstName=? AND lastName=? AND dob=?";
            pstmt = conn.prepareStatement(delete);
            pstmt.setString(1, key[0]);
            pstmt.setString(2, key[1]);
            pstmt.setString(3, key[2]);
            pstmt.executeUpdate();
            pstmt.clearParameters();

            delete = "DELETE FROM dHistory "
                    + "WHERE firstName=? AND lastName=? AND dob=?";
            pstmt = conn.prepareStatement(delete);
            pstmt.setString(1, key[0]);
            pstmt.setString(2, key[1]);
            pstmt.setString(3, key[2]);
            pstmt.executeUpdate();
            pstmt.clearParameters();

            //delete out of the lives_with table
            delete = "DELETE FROM lives_with WHERE dFirstName=? AND dLastName=? AND dDOB=?";
            pstmt = conn.prepareStatement(delete);
            pstmt.setString(1, key[0]);
            pstmt.setString(2, key[1]);
            pstmt.setString(3, key[2]);
            pstmt.executeUpdate();
            pstmt.clearParameters();

            //delete out of the family_composed_of table just to be sure
            delete = "DELETE FROM family_composed_of WHERE firstName=? AND lastName=? AND dob=?";
            pstmt = conn.prepareStatement(delete);
            pstmt.setString(1, key[0]);
            pstmt.setString(2, key[1]);
            pstmt.setString(3, key[2]);
            pstmt.executeUpdate();
            pstmt.clearParameters();

            //delete out of dependents table
            delete = "DELETE FROM dependents WHERE firstName=? AND lastName=? AND dob=?";
            pstmt = conn.prepareStatement(delete);
            pstmt.setString(1, key[0]);
            pstmt.setString(2, key[1]);
            pstmt.setString(3, key[2]);
            pstmt.executeUpdate();
            pstmt.clearParameters();
        }//else

        //DO NOT COMMIT!
    }//deleteDependent()

    /**
     * Deletes Family from the database. First from the Family_Composed_Of
     *          table, then Lives_with table, then In_Relationship_With
     *          table and then from the Adults table
     * @param key
     */
    private void deleteFamily(String[] key) throws SQLException{
        String delete;
        PreparedStatement pstmt;

        delete = "DELETE FROM fInterest "
                + "WHERE familyName=? AND primaryPhone=?";
        pstmt = conn.prepareStatement(delete);
        pstmt.setString(1, key[0]);
        pstmt.setString(2, key[1]);
        pstmt.executeUpdate();
        pstmt.clearParameters();

        delete = "DELETE FROM fHistory "
                + "WHERE familyName=? AND primaryPhone=?";
        pstmt = conn.prepareStatement(delete);
        pstmt.setString(1, key[0]);
        pstmt.setString(2, key[1]);
        pstmt.executeUpdate();
        pstmt.clearParameters();

        //delete out of the family_composed_of table
        delete = "DELETE FROM family_composed_of WHERE familyName=? AND primaryPhone=?";
        pstmt = conn.prepareStatement(delete);
        pstmt.setString(1, key[0]);
        pstmt.setString(2, key[1]);
        pstmt.executeUpdate();
        pstmt.clearParameters();

        delete = "DELETE FROM families "
                + "WHERE familyName=? AND primaryPhone=?";
        pstmt = conn.prepareStatement(delete);
        pstmt.setString(1, key[0]);
        pstmt.setString(2, key[1]);
        pstmt.executeUpdate();
        pstmt.clearParameters();

        //DO NOT COMMIT!
    }//deleteFamily()

    /**
     * Deletes Address from the database. First from Fam_Has_Address table,
     *          then from Address table
     * @param key
     */
    private void deleteAddress(String[] key) throws SQLException{
        String delete;
        PreparedStatement pstmt;

        //families can not share addresses because of isCurrentlyMailing attribute
        delete = "DELETE FROM fam_has_address "
            + "WHERE street=? AND zipcode=?";
        pstmt = conn.prepareStatement(delete);
        pstmt.setString(1, key[0]);
        pstmt.setString(2, key[1]);
        pstmt.executeUpdate();
        pstmt.clearParameters();

        delete = "DELETE FROM address "
                + "WHERE street=? AND zipcode=?";
        pstmt = conn.prepareStatement(delete);
        pstmt.setString(1, key[0]);
        pstmt.setString(2, key[1]);
        pstmt.executeUpdate();
        pstmt.clearParameters();

        //DO NOT COMMIT!
    }//deleteAddress()

    /**
     * Deletes Money from database. From Money table.
     * @param key
     * @param famKey
     * @throws SQLException
     */
    private void deleteMoney(String[] key, String[] famKey) throws SQLException{
        String delete;
        PreparedStatement pstmt;

        delete = "DELETE FROM money "
                + "WHERE theYear=? AND familyName=? AND primaryPhone=?";
        pstmt = conn.prepareStatement(delete);
        pstmt.setString(1, key[0]);
        pstmt.setString(2, famKey[0]);
        pstmt.setString(3, famKey[1]);
        pstmt.executeUpdate();
        pstmt.clearParameters();
        //DO NOT COMMIT!
    }//deleteMoney()

    /**
     *
     * @param prefix
     * @param key
     * @param famKey
     * @throws SQLException
     */
    private void deleteInterest(String prefix, String[] key, String[] personORfamKey) throws SQLException{
        String delete;
        PreparedStatement pstmt;
        String table = prefix + "Interest";

        if (prefix.equals("f")){
            delete = "DELETE FROM " + table + " " +
                     "WHERE familyName=? AND primaryPhone=? AND ministryName=?";
            pstmt = conn.prepareStatement(delete);
            pstmt.setString(1, personORfamKey[0]);
            pstmt.setString(2, personORfamKey[1]);
            pstmt.setString(3, key[0]);
            pstmt.executeUpdate();
            pstmt.clearParameters();
        }else{
            delete = "DELETE FROM " + table + " " +
                     "WHERE firstName=? AND lastName=? AND dob=? AND "
                     + "ministryName=?";
            pstmt = conn.prepareStatement(delete);
            pstmt.setString(1, personORfamKey[0]);
            pstmt.setString(2, personORfamKey[1]);
            pstmt.setString(3, personORfamKey[2]);
            pstmt.setString(4, key[0]);
            pstmt.executeUpdate();
            pstmt.clearParameters();
        }
        //DO NOT COMMIT!
    }//deleteInterest()

    /**
     *
     * @param prefix
     * @param key
     * @param famKey
     * @throws SQLException
     */
    private void deleteHistory(String prefix, String[] key, String[] personORfamKey) throws SQLException{
        String delete;
        PreparedStatement pstmt;
        String table = prefix + "History";

        if (prefix.equals("f")){
            delete = "DELETE FROM " + table + " " +
                     "WHERE familyName=? AND primaryPhone=? AND "
                     + "ministryName=? AND date=?";
            pstmt = conn.prepareStatement(delete);
            pstmt.setString(1, personORfamKey[0]);
            pstmt.setString(2, personORfamKey[1]);
            pstmt.setString(3, key[0]);
            pstmt.setString(4, key[1]);
            pstmt.executeUpdate();
            pstmt.clearParameters();
        }else{
            delete = "DELETE FROM " + table + " " +
                     "WHERE firstName=? AND lastName=? AND dob=? "
                     + "AND ministryName=? AND date=?";
            pstmt = conn.prepareStatement(delete);
            pstmt.setString(1, personORfamKey[0]);
            pstmt.setString(2, personORfamKey[1]);
            pstmt.setString(3, personORfamKey[2]);
            pstmt.setString(4, key[0]);
            pstmt.setString(5, key[1]);
            pstmt.executeUpdate();
            pstmt.clearParameters();
        }
        //DO NOT COMMIT!
    }//deleteHistory()



//COMMIT METHOD
    /**
     *
     * @return A boolean - true if the method was successful and false otherwise
     */
    public boolean commitAll(){
        boolean didCommit = false;

        //Commit ALL changes to database
        try{
            conn.commit();
            didCommit = true;
        }catch (SQLException e){
            System.err.println("Error in DBManager.commitAll(): " + e);
            try {
              System.err.println("Transaction is being rolled back");
              conn.rollback();
            } catch(SQLException e2) {
              System.err.println("Rolling back transcation unsucessful");
            }
        }

        return didCommit;
    }//commitAll()



//GET KEYS METHODS
   /**
     *
     * @return An ArrayList<String> where each String is a compilation of the
     *          attributes which comprise the key separated by a ';'.
     *          Example: "familyName;(000)000-0000"
     */
    public ArrayList<String> getAllFamilyKeys(){
        ArrayList<String> fk = new ArrayList<String>();
        PreparedStatement pstmt;
        ResultSet rs = null;
        String query;

        try{
            query = "SELECT familyName, primaryPhone FROM families ORDER BY familyName";
            pstmt = conn.prepareStatement(query);

            //query to database
            rs = pstmt.executeQuery();

            //loop through ResultSet gathering primary keys
            while (rs.next()){
                fk.add( rs.getString("familyName")+";"+ rs.getString("primaryPhone"));
            }
            rs.close();
        }catch (SQLException e){
            System.err.println("Error in DBManager.getAllFamilyKeys(): " + e);
        }

        return fk;
    }//getAllFamilyKeys()

    /**
     *
     * @param classification a String which specifies the keys to obtain.
     *                  The options are: "adults" or "dependents"
     *
     * @return An ArrayList<String> where each String is a compilation of the
     *          attributes which comprise the key separated by a ';'.
     *          Example: "firstName;lastName;dateOfBirth"
     */
    public ArrayList<String> getAllIndividualKeys(String classification){
        ArrayList<String> ak = new ArrayList<String>();
        PreparedStatement pstmt;
        ResultSet rs = null;
        String query;

        if (classification.equalsIgnoreCase("adults") || classification.equalsIgnoreCase("dependents")){
            try{
                query = "SELECT firstName, lastName, dob FROM "+classification+" ORDER BY lastName";
                pstmt = conn.prepareStatement(query);

                //query to database
                rs = pstmt.executeQuery();

                //loop through ResultSet gathering primary keys
                while (rs.next()){
                    ak.add( rs.getString("firstName")+";"+ rs.getString("lastName")+
                            ";"+ rs.getString("dob"));
                }
                rs.close();
            }catch (SQLException e){
                System.err.println("Error in DBManager.getAllAdultKeys(): " + e);
            }
        }

        return ak;
    }//getAllAdultKeys()



//MINISTRY METHODS
    /**
     *
     * @param ministryName a String containing the name of the ministry
     * @param isCommunitySerive a boolean indicating if the ministry is
     *                          community service(true) or not(false)
     * @param additionalNotes a String containing any additional notes for that
     *                          ministry
     * @return A boolean - true if the method was successful and false otherwise
     */
    public boolean addMinistry(String ministryName, boolean isCommunityService, String additionalNotes){
        String update;
        PreparedStatement pstmt;
        boolean didCommit = false;

        try{
            // Add to Ministries table
            update = "INSERT INTO ministries VALUES(?,?,?,?)";
            pstmt = conn.prepareStatement(update);
            pstmt.setString(1, ministryName);
            pstmt.setBoolean(2, isCommunityService);
            pstmt.setBoolean(3, true);
            pstmt.setString(4, additionalNotes);

            pstmt.executeUpdate();

            //Commit to database
            conn.commit();
            didCommit = true;
         }catch (SQLException e){
            System.err.println("Error in DBManager.addMinistry(): " + e);
            try {
              System.err.println("Transaction is being rolled back");
              conn.rollback();
            } catch(SQLException e2) {
              System.err.println("Rolling back transcation unsucessful");
            }
        }

        return didCommit;
    }//addMinistry()

    /**
     *
     * @param ministryName a String containing the name of the ministry which
     *                      attributes you want
     * @return A String array containing the attribute of the ministry in the
     *          following order: ministryName, isCommunityService, isActive,
     *          additionalNotes
     */
    public String[] getMinistry(String ministryName){
        ResultSet rs;
        PreparedStatement pstmt;
        String query;
        String[] ministry = new String[4];

        try{
            query = "SELECT ministryName, isCommunityService, notes FROM ministries WHERE ministryName=?";
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, ministryName);

            //query to database
            rs = pstmt.executeQuery();
            conn.commit(); //clear locks

            while (rs.next()){
                ministry[0] =  rs.getString("ministryName");
                ministry[1] =  rs.getBoolean("isCommunityService")+"";
                ministry[2] =  rs.getString("notes");
            }

            rs.close();
        }catch (SQLException e){
            System.err.println("Error in DBManager.getMinitry(): " + e);
        }

        return ministry;
    }//getMinistry()

    /**
     *
     * @param ministryName a String containing the name of the ministry
     * @param newStatus a boolean. True means activate. False means deactivate
     * @return A boolean - true if the method was successful and false otherwise
     */
    public boolean toggleMinistryActivationStatus(String ministryName, boolean newStatus){
        String update;
        PreparedStatement pstmt;
        boolean didCommit = true;

        try{
            // Add to Ministries table
            update = "UPDATE ministries SET isActive=? WHERE ministryName=?";
            pstmt = conn.prepareStatement(update);
            pstmt.setString(1, newStatus+"");
            pstmt.setString(2, ministryName);

            //commit to database
            pstmt.executeUpdate();
            conn.commit();
         }catch (SQLException e){
            System.err.println("Error in DBManager.toggleMinistryActivationStatus(): " + e);
            didCommit = false;
         }

        return didCommit;
    }//toggleMinistryActivationStatus()

    /**
     *
     * @param isActive a boolean. True will return active ministry names while
     *                  false will return de-active ministry names.
     * @return A String array containing the current active ministry names
     */
    public ArrayList<String> getMinistryNames(boolean isActive){
        ResultSet rs;
        PreparedStatement pstmt;
        ArrayList<String> ministries = new ArrayList<String>();
        String query;
        int counter = 0;

        try{
            query = "SELECT ministryName FROM ministries WHERE isActive=? ORDER BY ministryName";
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, isActive+"");

            //query to database
            rs = pstmt.executeQuery();
            conn.commit(); //clear locks

            while (rs.next()){
                //only one column and first column is 1 per ResultSet APIs
                ministries.add( rs.getString(1) );
                counter++;
            }

            rs.close();
        }catch (SQLException e){
            System.err.println("Error in DBManager.getActiveMinistryNames(): " + e);
        }

        return ministries;
    }//getMinistryNames()

    /**
     * Method does not edit 'isActive' attribute.
     * @param oldMinistryName a String containing the old name of the ministry
     * @param newMinistryName a String containing the new name of the ministry
     * @param isCommunitySerive a boolean indicating if the ministry is
     *                          community service(true) or not(false)
     * @param additionalNotes a String containing any additional notes for that
     *                          ministry
     * @return A boolean - true if the method was successful and false otherwise
     */
    public boolean editMinistry(String oldMinistryName, String newMinistryName,
                                    boolean isCommunitySerive, String additionalNotes){
        boolean didCommit = false;
        PreparedStatement pstmt;
        String update;

        try{
            if (oldMinistryName.equalsIgnoreCase(newMinistryName)){
                //just update the attributes
                update = "UPDATE ministries " +
                         "SET isCommunityService=?, notes=? " +
                         "WHERE ministryName=?";
                pstmt = conn.prepareStatement(update);
                pstmt.setBoolean(1, isCommunitySerive);
                pstmt.setString(2, additionalNotes);
                pstmt.setString(3,oldMinistryName);

                pstmt.executeUpdate();
            }else{
                //delete old one and add new one
                update = "DELETE FROM ministries WHERE ministryName=?";
                pstmt = conn.prepareStatement(update);
                pstmt.setString(1,oldMinistryName);

                pstmt.executeUpdate();
                pstmt.clearParameters();

                update = "INSERT INTO ministries VALUES(?,?,?,?)";
                pstmt = conn.prepareStatement(update);
                pstmt.setString(1,newMinistryName);
                pstmt.setBoolean(2, isCommunitySerive);
                pstmt.setBoolean(3, true);
                pstmt.setString(4, additionalNotes);

                pstmt.executeUpdate();
            }

            //Commit to database
            conn.commit();
            didCommit = true;
        }catch (SQLException e){
            System.err.println("Error in DBManager.editMinistry(): " + e);
            try {
              System.err.println("Transaction is being rolled back");
              conn.rollback();
            } catch(SQLException e2) {
              System.err.println("Rolling back transcation unsucessful");
            }
        }

        return didCommit;
    }//editMinistry()


//DEPRACTED METHODS
    /**
     * Purely for Testing Purposes. Not intended for actual implementation
     * @param ministryName a String containing the name of the ministry
     * @return A boolean - true if the method was successful and false otherwise
     */
    public boolean dM(String ministryName){
        String update;
        PreparedStatement pstmt;
        boolean didCommit = true;

        try{
            // Add to Ministries table
            update = "DELETE FROM ministries WHERE ministryName=?";
            pstmt = conn.prepareStatement(update);
            pstmt.setString(1, ministryName);

            //commit to database
            pstmt.executeUpdate();
            pstmt.clearParameters();
         }catch (SQLException e){
            System.err.println("Error in DBManager.dM(): " + e);
            didCommit = false;
         }

        return didCommit;
    }//dM()


    public boolean editIndividual(DBObject oldPersonInfo, DBObject newPersonInfo ){
        //Currently not fully implemented! Returns 'false'
        return false;
    }//editIndividual()

}//DBManager
